Re: BUG #14294: Problem in generate series between dates

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)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