Create list with first and last day of month for given period
date-range, generate-series, postgresql, sql
Solution
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
Problem
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.