pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: werner.fraga@computalog.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] datetime fields have '60' in seconds field
Date: Wed, 05 Jan 2000 10:32:22 -0500
Message-ID: <17223.947086342@sss.pgh.pa.us> (raw)
In-Reply-To: <99123115004704.02807@shorty.edmonton.computalog.com>
References: <99123115004704.02807@shorty.edmonton.computalog.com>

Werner Fraga <werner.fraga@computalog.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



view thread (5+ messages)  latest in thread

Message-ID: <17223.947086342@sss.pgh.pa.us>
Permalink:  ../17223.947086342@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17223.947086342@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, werner.fraga@computalog.com
  Subject: Re: [SQL] datetime fields have '60' in seconds field
  In-Reply-To: <17223.947086342@sss.pgh.pa.us>
  References: <99123115004704.02807@shorty.edmonton.computalog.com> <17223.947086342@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox