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

On 12/07/2014 12:11 PM, Sérgio Saquetim wrote:
> 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.

First generate_series is really looking for a timestamp on input and
returns timestamps:

http://www.postgresql.org/docs/9.3/interactive/functions-srf.html

generate_series(start, stop, step interval)     timestamp or timestamp with
time zone     setof timestamp or setof timestamp with time zone (same as
argument type)     Generate a series of values, from start to stop with a
step size of step

So:

test=# set timezone='Brazil/East';
SET

test=# SELECT generate_series('2014-10-10'::TIMESTAMP,
'2014-10-20'::TIMESTAMP, '1 DAY'::INTERVAL);
    generate_series
---------------------
  2014-10-10 00:00:00
  2014-10-11 00:00:00
  2014-10-12 00:00:00
  2014-10-13 00:00:00
  2014-10-14 00:00:00
  2014-10-15 00:00:00
  2014-10-16 00:00:00
  2014-10-17 00:00:00
  2014-10-18 00:00:00
  2014-10-19 00:00:00
  2014-10-20 00:00:00

test=# SELECT generate_series('2014-10-10'::TIMESTAMP WITH TIME ZONE,
'2014-10-20'::TIMESTAMP WITH TIME ZONE, '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
  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


Though this part I do not understand:

test=# select '2014-10-19'::timestamp with time zone;
       timestamptz
------------------------
  2014-10-19 01:00:00-02
(1 row)

test=# select '2014-10-20'::timestamp with time zone;
       timestamptz
------------------------
  2014-10-20 00:00:00-02
(1 row)

I thought interval understood 23/25 'day' across DST/ST boundaries. I
will have to think more on this.

>
> Thank you!
>
> Sérgio Saquetim
>


--
Adrian Klaver
adrian.klaver@aklaver.com