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.

>
> 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.

To follow up, it looks to be a Midnight issue. I live on the US West
Coast so:

test=# show timezone;
   TimeZone
------------
  US/Pacific
(1 row)

Our Spring change happened  March 9th at 2:00 AM:

test=# select '2014-03-09 01:00'::timestamp with time zone ;
       timestamptz
------------------------
  2014-03-09 01:00:00-08
(1 row)

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


When I do a similar generate_series:

test=# SELECT generate_series('2014-03-01'::DATE , '2014-03-10'::DATE ,
'1 DAY'::INTERVAL);
     generate_series
------------------------
  2014-03-01 00:00:00-08
  2014-03-02 00:00:00-08
  2014-03-03 00:00:00-08
  2014-03-04 00:00:00-08
  2014-03-05 00:00:00-08
  2014-03-06 00:00:00-08
  2014-03-07 00:00:00-08
  2014-03-08 00:00:00-08
  2014-03-09 00:00:00-08
  2014-03-10 00:00:00-07
(10 rows)


it works.

So it seems there is some confusion which Midnight is being used for the
DATE to timestamp with time zone conversion.



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


--
Adrian Klaver
adrian.klaver@aklaver.com