CODEWITHSUNDEEP
sqlpostgresqldate-rangegenerate-series
user3276142
user3276142

Reputation: 101

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.

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

The result should be in two columns:

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.

Upvotes: 5

Views: 3916

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

Add a month and subtract a day - in a single interval expression:

SELECT d AS mon_first
     , d + interval '1 month - 1 day' AS mon_last
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-06-30'
                     , interval  '1 month') d;

Further reading ("end of day" is very similar to "last day of month"):

About generating a time series:

Upvotes: 1

Houari
Houari

Reputation: 5621

Based on this answer:

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

Upvotes: 6

Related Questions