The following bug has been logged online:

Bug reference: 1180
Logged by: Ben Bidner

Email address: vort (AT) wiretapped (DOT) 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 (AT) wiretapped (DOT) net


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
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).

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Tue, 22 Jun 2004, Tom Lane wrote:

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)


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly