Thread: BUG #1180: incorrect date handling

The following bug has been logged online:

Bug reference:      1180
Logged by:          Ben Bidner

Email address:      vort@wiretapped.net

PostgreSQL version: 7.3.2

Operating system:   linux 2.4.20

Description:        incorrect date handling

Details:

colony=# CREATE TABLE foo (dtime TIMESTAMP);
CREATE TABLE
colony=# insert into foo values ('2003-12-11 23:59:60.00');
ERROR:  Bad timestamp external representation '2003-12-11 23:59:60.00'
colony=# insert into foo values ('2003-12-11 23:59:59.999999');
INSERT 97940618 1
colony=# select * from foo;
 dtime
----------------------------
 2003-12-11 23:59:59.999999
(1 row)

^-- correct

colony=# insert into foo values ('2003-12-11 23:59:59.9999999');
INSERT 97940623 1
colony=# select * from foo;
 dtime
----------------------------
 2003-12-11 23:59:59.999999
 2003-12-11 23:59:60.00
(1 row)

^-- incorrect

2003-12-11 23:59:60.00 should equal 2003-12-12 00:00:00

under 7.4.2, (had a friend test it out) inserting a timestamp with a time of
23:59:60.00 is allowed, it just increments the day and sets the time to
00:00:00 (as one would expect). However it still allows you to insert the
time as `23:59:59.9999999` and it updates the time (incorrectly) to
`23:59:60.00` instead of incrementing the day and setting the time to
00:00:00 (since 23:59:60 is .. or should be invalid).

vort@wiretapped.net

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> PostgreSQL version: 7.3.2

> colony=# insert into foo values ('2003-12-11 23:59:60.00');
> ERROR:  Bad timestamp external representation '2003-12-11 23:59:60.00'

Use a more recent PG version ... we have fixed a thing or two since
7.3 (in fact 7.3.3 and later versions take this, if I read the CVS logs
aright).

> under 7.4.2, (had a friend test it out) inserting a timestamp with a time of
> 23:59:60.00 is allowed, it just increments the day and sets the time to
> 00:00:00 (as one would expect). However it still allows you to insert the
> time as `23:59:59.9999999` and it updates the time (incorrectly) to
> `23:59:60.00` instead of incrementing the day and setting the time to
> 00:00:00 (since 23:59:60 is .. or should be invalid).

Horsepucky.

regression=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.4.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regression=# select '2003-12-11 23:59:60.00'::timestamp;
      timestamp
---------------------
 2003-12-12 00:00:00
(1 row)

            regards, tom lane

On Tue, 22 Jun 2004, Tom Lane wrote:

> "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> > PostgreSQL version: 7.3.2
>
> > colony=# insert into foo values ('2003-12-11 23:59:60.00');
> > ERROR:  Bad timestamp external representation '2003-12-11 23:59:60.00'
>
> Use a more recent PG version ... we have fixed a thing or two since
> 7.3 (in fact 7.3.3 and later versions take this, if I read the CVS logs
> aright).
>
> > under 7.4.2, (had a friend test it out) inserting a timestamp with a time of
> > 23:59:60.00 is allowed, it just increments the day and sets the time to
> > 00:00:00 (as one would expect). However it still allows you to insert the
> > time as `23:59:59.9999999` and it updates the time (incorrectly) to
> > `23:59:60.00` instead of incrementing the day and setting the time to
> > 00:00:00 (since 23:59:60 is .. or should be invalid).
>
> Horsepucky.
>
> regression=# select version();
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.4.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
> (1 row)
>
> regression=# select '2003-12-11 23:59:60.00'::timestamp;
>       timestamp
> ---------------------
>  2003-12-12 00:00:00
> (1 row)

That's not the case he was complaining about for 7.4 though, it was this
case I think (done on my 7.5 system, but...):

sszabo=# select timestamp '2003-12-11 23:59:59.9999999';
       timestamp
------------------------
 2003-12-11 23:59:60.00
(1 row)

sszabo=# select version();
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)