pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
datetime fields have '60' in seconds field
5+ messages / 3 participants
[nested] [flat]

* datetime fields have '60' in seconds field
@ 1999-12-31 19:06 Werner Fraga <werner.fraga@computalog.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Werner Fraga @ 1999-12-31 19:06 UTC (permalink / raw)
  To: pgsql-sql

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)
 
I am using Linux Mandrake 6.1, Postgres 6.5.1-7mdk, glibc 2.1.1-16mdk, kernel
2.2.13-7mdk



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* datetime fields have '60' in seconds field
@ 1999-12-31 20:18 Werner Fraga <werner.fraga@computalog.com>
  2000-01-05 15:32 ` Re: [SQL] datetime fields have '60' in seconds field Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Werner Fraga @ 1999-12-31 20:18 UTC (permalink / raw)
  To: pgsql-sql

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)
 
I am using Linux Mandrake 6.1, Postgres 6.5.1-7mdk, glibc 2.1.1-16mdk, kernel
2.2.13-7mdk




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] datetime fields have '60' in seconds field
  1999-12-31 20:18 datetime fields have '60' in seconds field Werner Fraga <werner.fraga@computalog.com>
@ 2000-01-05 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-07 19:19   ` Re: [SQL] datetime fields have '60' in seconds field Werner Fraga <werner.fraga@computalog.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2000-01-05 15:32 UTC (permalink / raw)
  To: werner.fraga@computalog.com; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] datetime fields have '60' in seconds field
  1999-12-31 20:18 datetime fields have '60' in seconds field Werner Fraga <werner.fraga@computalog.com>
  2000-01-05 15:32 ` Re: [SQL] datetime fields have '60' in seconds field Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-07 19:19   ` Werner Fraga <werner.fraga@computalog.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Werner Fraga @ 2000-01-07 19:19 UTC (permalink / raw)
  To: pgsql-sql

> 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)

Upgrade to Postgres 6.5.3 (redhat) from 6.5.1 (mandrake) fixed the problem.
Thanks.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] datetime fields have '60' in seconds field
@ 2000-01-05 00:37 omid omoomi <oomoomi@hotmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: omid omoomi @ 2000-01-05 00:37 UTC (permalink / raw)
  To: pgsql-sql

Hi,
I've just done the same case on my computer, seems that every thing is OK! :
( using freebsd  3.2 , pg 6.5.2 )
isp=> create table test( f1 text , f2 datetime);
CREATE
isp=> insert into test values('aaa','1999-12-31 12:05:40');
INSERT 51099 1
isp=> insert into test values('bbb','1999-12-31 12:05:00');
INSERT 51100 1
isp=> select * from test ;
f1 |f2
---+----------------------------
aaa|Fri Dec 31 12:05:40 1999 IRT
bbb|Fri Dec 31 12:05:00 1999 IRT
(2 rows)

--------------------


regards.
omid omoomi


>From: Werner Fraga <werner.fraga@computalog.com>
>Reply-To: werner.fraga@computalog.com
>To: pgsql-sql@postgreSQL.org
>Subject: [SQL] datetime fields have '60' in seconds field
>Date: Fri, 31 Dec 1999 13:18:18 -0700
>
>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)
>
>I am using Linux Mandrake 6.1, Postgres 6.5.1-7mdk, glibc 2.1.1-16mdk, 
>kernel
>2.2.13-7mdk
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-01-07 19:19 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-31 19:06 datetime fields have '60' in seconds field Werner Fraga <werner.fraga@computalog.com>
1999-12-31 20:18 datetime fields have '60' in seconds field Werner Fraga <werner.fraga@computalog.com>
2000-01-05 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-07 19:19   ` Werner Fraga <werner.fraga@computalog.com>
2000-01-05 00:37 Re: [SQL] datetime fields have '60' in seconds field omid omoomi <oomoomi@hotmail.com>

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