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

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