如何获得月，年和日在PostgreSQL的两个日期

问题描述：

我在SQL表是STARTDATE两列日期和结束日期如何获得月，年和日在PostgreSQL的两个日期

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

我想要得到的结果表像

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

应该是什么 “月” 值如startdate = 2017-01-01和enddate = 2017-06-29？或者startdate = 2010-04-01和enddate = 2017-02-01的年份值？ –

@a_horse_with_no_name第一个月值应为一月，二月，三月，四月，五月，六月，在一个月的日子里，包括开始日期和结束日期。而第二个日期年份分别为2010,2011,2012,2013,2014,2015,2016,2017，分别为日期和日期。 – dhS

请在添加要求之前添加评论。 – dhS

答

之间

粗略的解决方案是生成所有日子，然后聚合（计数）它们。它有效，但在记忆中很粗糙。如果这不是关键，这个解决方案肯定会奏效。另一种选择是生成一个月份系列，并在有很多条件的情况下进行日差，如果性能很关键。

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

在任何情况下，这里是第二个变体，通过几个月循环，而不是（更快，更难理解）：

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

这应该工作.. – dhS

我添加了“快”的版本，以防万一 – AlexanderMP

答

在这种特殊情况下，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 
$$;

测试：

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)

如何获得月，年和日在PostgreSQL的两个日期

相关推荐