Re: [SQL] datetime fields have '60' in seconds field

Werner Fraga <werner(dot)fraga(at)computalog(dot)com> 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