按月分组的两个日期之间的postgresql datediff

Question

我有一个包含日期列(开始日期、结束日期)的表格，我想计算这些日期之间的差异并按月分组。

我可以在几天内得到 datediff，但我不知道如何在月内对它进行分组，有什么建议吗？

Table:
id       Start_date   End_date      days      
1234     2014-06-03   2014-07-05     32
12345    2014-02-02   2014-05-10     97

Expected results:
month  diff_days
2      26
3      30
4      31
5      10
6      27    
7      5

Answer 1

我认为您的预期输出数字有点偏差。您可能需要仔细检查。

我自己使用日历表，但此查询使用 CTE 和日期算法。避免硬编码日期“2014-01-01”和 365 天的时间间隔很简单，但这会使查询更难阅读，所以我直接使用了这些值。

with your_data as (
  select date '2014-06-03' as start_date, date '2014-07-05' as end_date union all
  select '2014-02-02', '2014-05-10'
), calendar as (
  select date '2014-01-01' + (n || ' days')::interval calendar_date
  from generate_series(0, 365) n
)
select extract (month from calendar_date) calendar_month, count(*) from calendar
inner join your_data on calendar.calendar_date between start_date and end_date
group by calendar_month
order by calendar_month;

calendar_month  count--2               273               314               305               106               287               5

As a rule of thumb, you should never group by the month alone--doing that risks grouping data from different years. This is a safer version that includes the year, and which also restricts output to a single calendar year.

with your_data as (
  select date '2014-06-03' as start_date, date '2014-07-05' as end_date union all
  select '2014-02-02', '2014-05-10' 
), calendar as (
  select date '2014-01-01' + (n || ' days')::interval calendar_date
  from generate_series(0, 700) n
)
select extract (year from calendar_date) calendar_year, extract (month from calendar_date) calendar_month, count(*) from calendar
inner join your_data on calendar.calendar_date between start_date and end_date
where calendar_date between '2014-01-01' and '2014-12-31'
group by calendar_year, calendar_month
order by calendar_year, calendar_month;