创建包含给定时间段的每月第一天和最后一天的列表 [英] Create list with first and last day of month for given period

问题描述

我必须生成一个列表，其中在特定期间内每个月的两天间隔时间.第一列必须是月份的第一天，第二列必须是月份的最后一天.

I have to generate a list with two columns of day intervals for every month in a specific period. First column must be the first day of month and the second column the last day of month.

示例:
开始日期:2014-01-01
结束日期:2014-06-30

Example:
Start date: 2014-01-01
End date: 2014-06-30

结果应该在两列中:

1. 2014-01-01 | 2014-01-31
2. 2014-02-01 | 2014-02-28
3. 2014-03-01 | 2014-03-31
4. 2014-04-01 | 2014-04-30
5. 2014-05-01 | 2014-05-31
6. 2014-06-01 | 2014-06-30

我有两个函数可以从日期中获取第一天和最后一天. 我正在尝试将两个系列结合起来，但是没有运气.

I have two functions that get the first and last day from a date. I am trying to combine two series but with no luck.

select i::date
from generate_series(first_day('2014-01-01')
                    ,first_day('2014-06-30'), '1 month'::interval) i

select i::date
from generate_series(last_day('2014-01-01')
                    ,last_day('2014-06-30'), '1 month'::interval) i

第二个功能在系列中时不能正确显示最后一天.

The second function does not show the last day correctly when it is in the series.

推荐答案

基于此答案:

select d::date as start_date,(d + '1 month'::interval - '1 day'::interval )::date end_date
from generate_series('2014-01-01'::date, '2014-06-30'::date, '1 month'::interval) d

这篇关于创建包含给定时间段的每月第一天和最后一天的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！