Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> This _looks_ wrong:
> set timezone = 'America/Santiago';
> select timestamptz '2016-08-13', timestamptz '2016-08-13' + interval '1 day';
> timestamptz | ?column?
> ------------------------+------------------------
> 2016-08-13 00:00:00-04 | 2016-08-14 01:00:00-03
> (1 row)
> but I guess it's inevitable, since 2016-08-14 00:00:00 doesn't exist in
> the local time, so there's no other possible result to return.
Yeah. And after that, the shift persists, eg.
# select '2016-08-14 01:00:00-03'::timestamptz + '1 day'::interval;
?column?
------------------------
2016-08-15 01:00:00-03
(1 row)
That's a bit annoying, because it works if you skip over that day:
# select '2016-08-13'::timestamptz + '2 days'::interval;
?column?
------------------------
2016-08-15 00:00:00-03
(1 row)
In other words, we could make this scenario "work" if we defined
generate_series as base plus N times the increment, rather than as
repeated addition of the increment. But I wouldn't be surprised
if that would break other corner cases (and it would certainly
be slower). Daylight-savings time is not one of the more consistent
things in our world ... not that anything at all about the civil
calendar is mathematically nice :-(.
Certainly, the right answer in this example case is to use
the timestamp not timestamptz flavor of generate_series.
Or you could use the integer flavor and add the results to
a base date using the date + integer operator.
regards, tom lane