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

You've nailed it, thank you!

Finally I'm understanding what's going on.

I wasn't paying attention to the fact that generate_series really expects
for timezone inputs. So when I was passing the upper bound
as '2014-10-20'::DATE, the value was being cast to 2014-10-20 00:00:00-02.

postgres=# SELECT '2014-10-20'::TIMESTAMPTZ;
timestamptz
------------------------
2014-10-20 00:00:00-02
(1 row)

But after the DST change the generate_series changes the hour in the
generated values as in 2014-10-20 01:00:00-02, which is bigger
than 2014-10-20 00:00:00-02 and because of that it's not returned.

Using a larger upper bound solved my problem.

postgres=# SELECT generate_series('2014-10-15 00:00:00'::TIMESTAMPTZ,
'2014-10-20 23:59:59'::TIMESTAMPTZ, '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
2014-10-20 01:00:00-02
(6 rows)

Thank you again!

Sérgio Saquetim

2014-12-07 20:04 GMT-02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> 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(dot)klaver(at)aklaver(dot)com
>