Strange behavior in generate_series(date, date, interval) with DST

From: Sérgio Saquetim <sergiosaquetim(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange behavior in generate_series(date, date, interval) with DST
Date: 2014-12-07 20:11:02
Message-ID: CACwnGOenAq7ug7yUgOuWEn52aTvi4ySQN1H5PYjru3t5B7HnJg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've noticed a strange behavior in the generate_series functions.

I'm trying to get all days between a start and an end date including the
bounds. So naturally I've tried something like the query below
​.

The real query uses generate_series to join other tables and is much more
complicated, but for the sake of brevity, I think that this query is good
enough to show the problem.
:

postgres=# SELECT generate_series('2014-10-10'::DATE,
​​
'2014-10-15'::DATE, '1 DAY'::INTERVAL);
generate_series
------------------------
2014-10-10 00:00:00-03
2014-10-11 00:00:00-03
2014-10-12 00:00:00-03
2014-10-13 00:00:00-03
2014-10-14 00:00:00-03
2014-10-15 00:00:00-03
​​
(6 rows)

​Please note that the upper bound ​

'2014-10-15' is included in the resulting rows.

Now if I try this same query with slightly different dates I get:

​postgres=# SELECT generate_series('2014-10-15'::DATE, '2014-10-20'::DATE,
'1 DAY'::INTERVAL);
generate_series
------------------------
2014-10-15 00:00:00-03
2014-10-16 00:00:00-03
2014-10-17 00:00:00-03
2014-10-18 00:00:00-03
2014-10-19 01:00:00-02
(5 rows)

-----------------------------------------------
The upper bound is not included in the results!
-----------------------------------------------

Here, in Brazil our DST started on Oct 19. So if I had to guess I would say
that this strange behavior is due to the DST, but I'm having a hard time to
understand why this is happening!

Is this expected behavior?

I know that I can achieve the results I expect with the following query:

postgres=# WITH RECURSIVE days(d) AS (
SELECT '2014-10-15'::DATE
UNION ALL
SELECT d+1 FROM days WHERE d < '2014-10-20'::DATE
)
SELECT * FROM days;
d
------------
2014-10-15
2014-10-16
2014-10-17
2014-10-18
2014-10-19
2014-10-20
(6 rows)

But using that instead of generate_series, just feels wrong so I would like
to understand what's happening and if there is a way to overcome that,
before changing my queries.

Thank you!

Sérgio Saquetim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-12-07 20:49:17 Re: Strange behavior in generate_series(date, date, interval) with DST
Previous Message AJ Welch 2014-12-07 04:40:08 Use cases for lateral that do not involve a set returning function