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.klaver@xxxxxxxxxxx>:
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.
