Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA41432 for ; Wed, 5 Jan 2000 10:32:55 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA17226; Wed, 5 Jan 2000 10:32:22 -0500 (EST) To: werner.fraga@computalog.com cc: pgsql-sql@postgresql.org Subject: Re: [SQL] datetime fields have '60' in seconds field In-reply-to: <99123115004704.02807@shorty.edmonton.computalog.com> References: <99123115004704.02807@shorty.edmonton.computalog.com> Comments: In-reply-to Werner Fraga message dated "Fri, 31 Dec 1999 13:18:18 -0700" Date: Wed, 05 Jan 2000 10:32:22 -0500 Message-ID: <17223.947086342@sss.pgh.pa.us> From: Tom Lane Werner Fraga writes: > When a DATETIME field is modified and the seconds are set to zero, the field > always shows 60 seconds instead: > testdb=> create table datetab (dt datetime); > CREATE > testdb=> insert into datetab (dt) values ('1999-12-31 12:05:00'); > INSERT 27361 1 > testdb=> select * from datetab; > dt > -------------------------- > 31/12/1999 12:05:60.00 MST > (1 row) Hmm. I can produce a similar output by playing games with the input: play=> SET DATESTYLE TO 'SQL'; SET VARIABLE play=> insert into datetab (dt) values ('1999-12-31 12:04:59.999999'); INSERT 2013029 1 play=> insert into datetab (dt) values ('1999-12-31 12:04:59.9999999'); INSERT 2013030 1 play=> insert into datetab (dt) values ('1999-12-31 12:04:59.99999999'); INSERT 2013031 1 play=> select *, date_part('epoch', dt) from datetab; dt | date_part --------------------------+---------------- 31/12/1999 12:04:60.00 EST|946659899.999999 31/12/1999 12:05:00.00 EST| 946659900 31/12/1999 12:05:00.00 EST| 946659900 (3 rows) but as you can see the minutes part is 04, so this is a correctly rounded (even though odd-looking) display of the given time value. Your result is not correct. > I am using Linux Mandrake 6.1, Postgres 6.5.1-7mdk, glibc 2.1.1-16mdk, > kernel 2.2.13-7mdk Never heard of that version of Linux. Is it possible that it has busted math routines? Breakdown of hours/minutes/seconds for display is handled by dt2time() in src/backend/utils/adt/dt.c, and I'm really at a loss to see how it could produce such an incorrect result unless the C compiler and library fail to implement "int = (double / 60)" per spec. You might try inserting some debugging printouts in dt2time to see what's going on... regards, tom lane