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

Hello,

Thanks to everyone, actually the timezone is America/Santiago (no idea how
you noticed this really), and it actually appears that the problem is with
the 14th 00:00:00 not existing.

I've tried what you suggested SELECT generate_series(timestamp
'2016-08-01', '2016-08-31', '1 day'::interval)::date; and it worked.

Another solution, that is the one i'm actually using right now is:

SELECT ('2016-08-01'::date + (interval '1' day * generate_series(0,30)) )::
date

Thanks for your help.

Best regards,

2016-08-24 17:10 GMT-03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> 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
>

--
Pablo Pumarino D.
+569 82781776