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

Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
> On 1 September 2016 at 00:39, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
> "Dean" == Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
>>>> Perhaps adding generate_series(date,date,interval) might work.

> Hmm, maybe, but since this is timezone-dependent, the existing code
> might be perfectly safe in the user's part of the world. I don't
> really have a feel for how likely this is to break people's code, but
> I think it's something we have to consider.

Consider this perfectly reasonable use-case:

# select generate_series(current_date,current_date+1,interval '1 hour');
generate_series
------------------------
2016-09-01 00:00:00-04
2016-09-01 01:00:00-04
2016-09-01 02:00:00-04
2016-09-01 03:00:00-04
2016-09-01 04:00:00-04
2016-09-01 05:00:00-04
2016-09-01 06:00:00-04
2016-09-01 07:00:00-04
2016-09-01 08:00:00-04
2016-09-01 09:00:00-04
2016-09-01 10:00:00-04
2016-09-01 11:00:00-04
2016-09-01 12:00:00-04
2016-09-01 13:00:00-04
2016-09-01 14:00:00-04
2016-09-01 15:00:00-04
2016-09-01 16:00:00-04
2016-09-01 17:00:00-04
2016-09-01 18:00:00-04
2016-09-01 19:00:00-04
2016-09-01 20:00:00-04
2016-09-01 21:00:00-04
2016-09-01 22:00:00-04
2016-09-01 23:00:00-04
2016-09-02 00:00:00-04
(25 rows)

We can't make the data type of the output dependent on the interval size,
so decreeing that this now produces date not timestamp would break any
case with a fractional-day interval.

After reviewing the previous thread, I have no real desire to take
this up again. The consensus then was that the added utility didn't
outweigh the likelihood of breaking existing queries, and we've not
covered anything here that wasn't discussed before.

Maybe we just need an example in the docs about working with dates.

regards, tom lane