如何在两个日期之间的postgresql中获取月，年和日

I have two columns in the sql table which is startdate and enddate

我在sql表中有两列是startdate和enddate

Startdate           Enddate
27-12-2015 22:30    03-01-2016 19:30
01-01-2016 12:45    09-02-2016 18:30

I want to get the resultant table like

我想得到像这样的结果表

Startdate           Enddate            Month     year   days
27-12-2015 22:30    03-01-2016 19:30   Dec       2015   5
27-12-2015 22:30    03-01-2016 19:30   Jan       2016   3
01-01-2016 12:45    09-02-2016 18:30   Jan       2016   31
01-01-2016 12:45    09-02-2016 18:30   Feb       2016   9

2 个解决方案

#1

A rough solution would be to generate all the days and then aggregate (count) them. It works, but it's rough on memory. If it's not crucial, this solution would definitely work. The alternative is to generate a months series and make a day diff with a lot of conditions, if performance is critical.

一个粗略的解决方案是生成所有日子然后聚合（计数）它们。它有效，但记忆力很差。如果它不重要，那么这个解决方案肯定会奏效。如果性能至关重要，另一种方法是生成一个月系列，并在很多条件下进行一天的差异化。

SELECT
    dates.startdate::DATE,
    dates.enddate::DATE,
    to_char(days.s, 'Mon') AS mon,
    to_char(days.s, 'YYYY') AS yr,
    count(1) AS d
FROM dates
CROSS JOIN LATERAL (
    SELECT * FROM generate_series(dates.startdate, dates.enddate, INTERVAL '1 day') s
) days
GROUP BY 1, 2, 3, 4

In any case, here is the second variant, that loops through months instead (faster, harder to understand):

在任何情况下，这是第二个变体，它循环数月（更快，更难理解）：

SELECT
    dates.startdate::DATE,
    dates.enddate::DATE,
    to_char(months.startdate, 'Mon') AS mon,
    to_char(months.startdate, 'YYYY') AS yr,
    least(
        months.enddate::DATE - dates.startdate::DATE + 1, -- takes care of first month
        dates.enddate::DATE - months.startdate::DATE + 1, -- takes care of last month
        months.enddate::DATE - months.startdate::DATE + 1 -- takes care of full months from the middle of the intervals
    ) AS "days"
FROM dates
-- get months as first day in that month
CROSS JOIN LATERAL (
    SELECT * FROM generate_series(
        (to_char(dates.startdate, 'YYYY-MM-') || '01')::DATE, 
        (to_char(dates.enddate + INTERVAL '1 month', 'YYYY-MM-') || '01')::DATE - 1, INTERVAL '1 month') m
) days
-- get months as start date and end date
CROSS JOIN LATERAL (
    SELECT
        days.m::DATE AS startdate,
        (days.m + INTERVAL '1 month')::DATE - 1 AS enddate
) months

#2

In this particular case a plpgsql function can provide better performance than a plain sql query.

在这种特殊情况下，plpgsql函数可以提供比普通sql查询更好的性能。

create or replace function get_months(startdate date, enddate date)
returns table (mon text, year int, days int)
language plpgsql as $$
declare d date;
begin
    d:= date_trunc('month', startdate);
    while d < enddate loop
        mon:= to_char(d, 'Mon');
        year:= to_char(d, 'YYYY');
        days:= case 
            when d+ '1month'::interval > enddate then enddate- d+ 1
            when d < startdate then (d+ '1month'::interval)::date- startdate
            else (d+ '1month'::interval)::date- d
        end;
        return next;
        d:= d+ '1month'::interval;
    end loop;
end
$$;

Test:

测试：

with my_table(startdate, enddate) as (
    values
    ('2015-12-27 22:30', '2016-01-03 19:30'),
    ('2016-01-01 12:45', '2016-02-09 18:30')
)

select *
from my_table, 
lateral get_months(startdate::date, enddate::date)

    startdate     |     enddate      | mon | year | days 
------------------+------------------+-----+------+------
 2015-12-27 22:30 | 2016-01-03 19:30 | Dec | 2015 |    5
 2015-12-27 22:30 | 2016-01-03 19:30 | Jan | 2016 |    3
 2016-01-01 12:45 | 2016-02-09 18:30 | Jan | 2016 |   31
 2016-01-01 12:45 | 2016-02-09 18:30 | Feb | 2016 |    9
(4 rows)

#1

SELECT
    dates.startdate::DATE,
    dates.enddate::DATE,
    to_char(days.s, 'Mon') AS mon,
    to_char(days.s, 'YYYY') AS yr,
    count(1) AS d
FROM dates
CROSS JOIN LATERAL (
    SELECT * FROM generate_series(dates.startdate, dates.enddate, INTERVAL '1 day') s
) days
GROUP BY 1, 2, 3, 4