[GENERAL] Strange behavior in generate_series(date, date, interval) with DST

[Sérgio Saquetim]

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

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

[Sérgio Saquetim]

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 :

> 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.kla...@aklaver.com
>