BUG #14294: Problem in generate series between dates

"pablopumarino" == pablopumarino <pablopumarino@gmail.com> writes:

pablopumarino> SELECT generate_series('2016-08-01', '2016-08-31', '1 day'::interval)::date

Does it work better if you do:

SELECT generate_series(timestamp '2016-08-01', '2016-08-31', '1 day'::interval)::date;

?

(Your version is using timestamp with time zone, which is probably not a
good idea)

Also, what is the output you get for:

SELECT d, d::date FROM generate_series('2016-08-01', '2016-08-31', '1 day'::interval) AS d;

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

pablopumarino@gmail.com writes:

When I use postgres function:
SELECT generate_series('2016-08-01', '2016-08-31', '1
day'::interval)::date
it gives me the corresponding list of days but for some reason it misses the
day 2016-08-31.

In addition to Andrew's questions:

* What time zone are you using?
* In that zone, is there a DST rule change near 2016-08-31?

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 8/24/2016 11:29 AM, pablopumarino@gmail.com wrote:

SELECT generate_series('2016-08-01', '2016-08-31', '1
day'::interval)::date

it gives me the corresponding list of days but for some reason it misses the
day 2016-08-31.
-I've tried this with other months and it works correctly, also if I use it
for the same dates but on 2015 it works fine.
-This started failing after we upgraded our system to the version 9.5.4,
I've tryed it in an older release that we had installed and it worked.

I would write that as...

select generate_series(timestamp '2016-08-01', timestamp '2016-08-31',
interval '1 day')::date;

the notation type 'string value' is a native constant in that type,
while the notation 'string value'::type generates a text constant and
typecasts it.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

John R Pierce <pierce@hogranch.com> writes:

the notation type 'string value' is a native constant in that type,
while the notation 'string value'::type generates a text constant and
typecasts it.

Actually, no, there's no difference. If the argument of ::type is
a simple string literal, it's just fed to the type's input parser.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

When I use postgres function:
SELECT generate_series('2016-08-01', '2016-08-31', '1
day'::interval)::date
it gives me the corresponding list of days but for some reason it misses the
day 2016-08-31.

Tom> In addition to Andrew's questions:

Tom> * What time zone are you using?

I discovered that this is reproducible in America/Santiago

Tom> * In that zone, is there a DST rule change near 2016-08-31?

The DST change seems to be at midnight local on 2016-08-14 (who puts
their DST changes at midnight? that breaks things here)

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.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@sss.pgh.pa.us>:

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

-- 
Pablo Pumarino D.
+569 82781776

Re: Tom Lane 2016-08-24 <31168.1472069440@sss.pgh.pa.us>

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 :-(.

To avoid DST problems, wouldn't a "date"-based generate_series be the
safe way to go?

Christoph

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Christoph" == Christoph Berg <myon@debian.org> writes:

Christoph> To avoid DST problems, wouldn't a "date"-based
Christoph> generate_series be the safe way to go?

The lack of generate_series(date,date,integer) is sometimes annoying,
even though it can be worked around using the timestamp-without-timezone
variant of generate_series.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:

"Christoph" == Christoph Berg <myon@debian.org> writes:
Christoph> To avoid DST problems, wouldn't a "date"-based
Christoph> generate_series be the safe way to go?

The lack of generate_series(date,date,integer) is sometimes annoying,
even though it can be worked around using the timestamp-without-timezone
variant of generate_series.

Or you can do it with "base_date + generate_series(integer...)".

I think we looked at this when the timestamp generate_series functions
were put in, and were worried about overloading the name so far that
common use-cases would get ambiguous-function failures. If that can
be shown not to happen, though, it'd be worth adding such a function
IMO.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

The lack of generate_series(date,date,integer) is sometimes
annoying, even though it can be worked around using the
timestamp-without-timezone variant of generate_series.

Tom> Or you can do it with "base_date + generate_series(integer...)".

Tom> I think we looked at this when the timestamp generate_series
Tom> functions were put in, and were worried about overloading the name
Tom> so far that common use-cases would get ambiguous-function
Tom> failures. If that can be shown not to happen, though, it'd be
Tom> worth adding such a function IMO.

I don't see why there would be ambiguity. date_part already has
overloads for every date/time type without causing any issues (but
date_trunc does not, which is another source of subtle timezone bugs).

Some experimentation with creating
pg_catalog.generate_series(date,date,integer) and trying the usual
use-cases doesn't seem to turn up any issues. Looking at the list of
implicit casts also doesn't suggest that there would be any problems.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 31 August 2016 at 23:07, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

Some experimentation with creating
pg_catalog.generate_series(date,date,integer) and trying the usual
use-cases doesn't seem to turn up any issues. Looking at the list of
implicit casts also doesn't suggest that there would be any problems.

Adding generate_series(date,date,integer) was attempted a recently
[1]: /messages/by-id/56EAB874.9040205@pgmasters.net
currently work. At the time the new function was presented merely as a
convenience to save having to cast results, but I think this
discussion adds more weight to the argument for such a function.
Perhaps adding generate_series(date,date,interval) might work.

[1]: /messages/by-id/56EAB874.9040205@pgmasters.net

Regards,
Dean

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 1 September 2016 at 00:05, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

Perhaps adding generate_series(date,date,interval) might work.

On second thoughts, that would change the return type of some existing
queries, which would be problematic.

Regards,
Dean

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Dean" == Dean Rasheed <dean.a.rasheed@gmail.com> writes:

Perhaps adding generate_series(date,date,interval) might work.

Dean> On second thoughts, that would change the return type of some
Dean> existing queries, which would be problematic.

But as this bug report is showing, those existing queries are at best
fragile and at worst silently wrong...

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 1 September 2016 at 00:39, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

"Dean" == Dean Rasheed <dean.a.rasheed@gmail.com> writes:

Perhaps adding generate_series(date,date,interval) might work.

Dean> On second thoughts, that would change the return type of some
Dean> existing queries, which would be problematic.

But as this bug report is showing, those existing queries are at best
fragile and at worst silently wrong...

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.

Regards,
Dean

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On 1 September 2016 at 00:39, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
"Dean" == Dean Rasheed <dean.a.rasheed@gmail.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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs