4

I want to generate a series of months within a specific time period.

I have tried:

select  GENERATE_SERIES( '06/25/2016'::DATE, '12/15/2016'::DATE, '1 month' )

It returns:

"generate_series"
"2016-06-25 00:00:00-06"
"2016-07-25 00:00:00-06"
"2016-08-25 00:00:00-06"
"2016-09-25 00:00:00-06"
"2016-10-25 00:00:00-06"
"2016-11-25 00:00:00-07"

There is an issue at the end date. I want the result as follows:

"generate_series"
 "2016-06-25 00:00:00-06"
 "2016-07-25 00:00:00-06"
 "2016-08-25 00:00:00-06"
 "2016-09-25 00:00:00-06"
 "2016-10-25 00:00:00-06"
 "2016-11-25 00:00:00-07"
 "2016-12-15 00:00:00-07"

I am using PostgreSQL.

0
5

Evan explains why the generate_series() cannot do by itself what you want. But it can be done with a simple UNION:

select generate_series('2016-06-25'::timestamp, '2016-12-15', '1 month') 
         as g_date 
union 
select '2016-12-15'
-- order by g_date 
;

or UNION ALL:

select generate_series('2016-06-25'::timestamp, 
                       '2016-12-15' - interval '1 day', 
                       '1 month') as g_date 
union all
select '2016-12-15' ;

Also note that generate_series() output is of timestamp without timezone type. if you want a date type, you need to cast:

select generate_series('2016-06-25'::timestamp, 
                       '2016-12-15' - interval '1 day', 
                       '1 month')::date as g_date 
union all
select '2016-12-15'::date ;
0
4

The function you're calling has a signature of generate_series(start, stop, step interval). Because of this it can never generate a data point after the stop argument. It stops. So for sanity, because you want the 25th day of the month, what you need is for

  1. stop to be hard-coded after the 25th, such as the end of month

    SELECT ts
    FROM generate_series(
      '06/25/2016'::DATE,
      '12/31/2016'::DATE,
      '1 month'
    ) AS t(ts);
    
  2. start and stop to be the first month, and then you just add 24 days to get the 25th day.

    SELECT ts + '24 days'
    FROM generate_series(
      '06/1/2016'::DATE,
      '12/1/2016'::DATE,
      '1 month'
    ) AS t(ts);
    
  3. To calculate a stop point after the 25th day... such as this method.. calculates last day of the month.

    SELECT ts
    FROM generate_series(
      '06/25/2016'::DATE,
      (date_trunc('month', '12/15/2016'::date) + '1 month - 1 day')::DATE,
      '1 month'
    ) AS t(ts);
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.