Using generate_series with timezone

Question

I'm trying to generate a series that shows the first day of every month in PostgreSQL

select generate_series('2020-01-01T00:00:00+03:00'::timestamptz, '2020-12-31T23:59:59+03:00'::timestamptz, '1 month');

this query returns that result

    generate_series     
------------------------
 2019-12-31 21:00:00+00
 2020-01-31 21:00:00+00
 2020-02-29 21:00:00+00
 2020-03-29 21:00:00+00
 2020-04-29 21:00:00+00
 2020-05-29 21:00:00+00
 2020-06-29 21:00:00+00
 2020-07-29 21:00:00+00
 2020-08-29 21:00:00+00
 2020-09-29 21:00:00+00
 2020-10-29 21:00:00+00
 2020-11-29 21:00:00+00
 2020-12-29 21:00:00+00
(13 rows)

but I expect that

    generate_series     
------------------------
 2019-12-31 21:00:00+00
 2020-01-31 21:00:00+00
 2020-02-29 21:00:00+00
 2020-03-31 21:00:00+00
 2020-04-30 21:00:00+00
 2020-05-31 21:00:00+00
 2020-06-30 21:00:00+00
 2020-07-31 21:00:00+00
 2020-08-31 21:00:00+00
 2020-09-30 21:00:00+00
 2020-10-31 21:00:00+00
 2020-11-30 21:00:00+00
(12 rows)

Where did I go wrong?

also there is no problem in using this way

select generate_series('2020-01-01T00:00:00'::timestamptz, '2020-12-31T23:59:59'::timestamptz, '1 month');
    generate_series     
------------------------
 2020-01-01 00:00:00+00
 2020-02-01 00:00:00+00
 2020-03-01 00:00:00+00
 2020-04-01 00:00:00+00
 2020-05-01 00:00:00+00
 2020-06-01 00:00:00+00
 2020-07-01 00:00:00+00
 2020-08-01 00:00:00+00
 2020-09-01 00:00:00+00
 2020-10-01 00:00:00+00
 2020-11-01 00:00:00+00
 2020-12-01 00:00:00+00

user_6464308 · Answer

generate_series works iteratively, so it calculates the next element by adding the step to the precious element.

The problem is that the calculation is done in your current time zone. The exact meaning of "one month later" unfortunately depends on the time zone...

To make this work as expected, change your time tone to match the time zone in the statement:

SET timezone='+03';

Using generate_series with timezone

1 Answers