Обсуждение: Some timestamp problems
Greetings,
I found some strange behaviour in 7.1.2. Is it the same thing on 7.2.2 ?
I don't know if it's a psql bug, but I would like to report it anyway
bxs=# \d test2
Table "test2"
Attribute | Type | Modifier
------------------+--------------------------+----------
x | integer |
apagar_menor_que | timestamp with time zone |
bxs=# insert into test2 values(1, '2111-10-01 04:23:59.999');
INSERT 259672 1
bxs=# insert into test2 values(2, '20001-10-01 04:23:50');
INSERT 259673 1
bxs=# insert into test2 values(3, '20001-10-01 04:23:59');
INSERT 259674 1
bxs=# insert into test2 values(4, '20001-10-01 04:23:59.99');
INSERT 259675 1
bxs=# insert into test2 values(5, '20001-10-01 04:23:59.999');
INSERT 259676 1
bxs=# insert into test2 values(6, '20001-10-01 04:23:59.998');
INSERT 259677 1
bxs=# SELECT * FROM test2;
x | apagar_menor_que
---+------------------------
1 | 2111-10-01 04:23:60.00
2 | 20001-10-01 04:2350
3 | 20001-10-01 04:2359
4 | 20001-10-01 04:2359.99
5 | 20001-10-01 04:2360.00
6 | 20001-10-01 04:2360.00
(6 rows)
bxs=#
Best regards from Brazil,
----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software Engineer
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
I think there is plenty of time to address the Y10K problem... :) JLL Vilson farias wrote: > > Greetings, > > I found some strange behaviour in 7.1.2. Is it the same thing on 7.2.2 ? > > I don't know if it's a psql bug, but I would like to report it anyway > > bxs=# \d test2 > Table "test2" > Attribute | Type | Modifier > ------------------+--------------------------+---------- > x | integer | > apagar_menor_que | timestamp with time zone | > > bxs=# insert into test2 values(1, '2111-10-01 04:23:59.999'); > INSERT 259672 1 > bxs=# insert into test2 values(2, '20001-10-01 04:23:50'); > INSERT 259673 1 > bxs=# insert into test2 values(3, '20001-10-01 04:23:59'); > INSERT 259674 1 > bxs=# insert into test2 values(4, '20001-10-01 04:23:59.99'); > INSERT 259675 1 > bxs=# insert into test2 values(5, '20001-10-01 04:23:59.999'); > INSERT 259676 1 > bxs=# insert into test2 values(6, '20001-10-01 04:23:59.998'); > INSERT 259677 1 > bxs=# SELECT * FROM test2; > x | apagar_menor_que > ---+------------------------ > 1 | 2111-10-01 04:23:60.00 > 2 | 20001-10-01 04:2350 > 3 | 20001-10-01 04:2359 > 4 | 20001-10-01 04:2359.99 > 5 | 20001-10-01 04:2360.00 > 6 | 20001-10-01 04:2360.00 > (6 rows) > > bxs=# > > Best regards from Brazil, > > ---------------------------------------------------------------------------- > ---- > José Vilson de Mello de Farias > Software Engineer > > Dígitro Tecnologia Ltda - www.digitro.com.br > APC - Customer Oriented Applications > E-mail: vilson.farias@digitro.com.br > Tel.: +55 48 281 7158 > ICQ 11866179 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
But there are two kind of problems there. First related with big date values. ok, bot big deal yet :), but the second is taking my attention : if you put a time with 995 to 999 miliseconds, it's represented as 60seconds instead of 59seconds and 995 to 999 miliseconds. I don't think it's alright, because I've never seen a datetime with 60seconds value (60 seconds is always the next minute, in my conception). I would like some light here, is it just a psql problem to show datetime values or a internal datetime storage problem? New example: bxs=# insert into test2 values(1, '2002-08-29 04:23:59.994'); INSERT 259868 1 bxs=# insert into test2 values(2, '2002-08-29 04:23:59.995'); INSERT 259869 1 bxs=# insert into test2 values(2, '2002-08-29 04:23:59.996'); INSERT 259870 1 bxs=# insert into test2 values(3, '2002-08-29 04:23:59.997'); INSERT 259871 1 bxs=# insert into test2 values(4, '2002-08-29 04:23:59.998'); INSERT 259872 1 bxs=# insert into test2 values(5, '2002-08-29 04:23:59.999'); INSERT 259873 1 bxs=# select * from test2; x | apagar_menor_que ---+--------------------------- 1 | 2002-08-29 04:23:59.99-03 2 | 2002-08-29 04:23:59.99-03 2 | 2002-08-29 04:23:60.00-03 3 | 2002-08-29 04:23:60.00-03 4 | 2002-08-29 04:23:60.00-03 5 | 2002-08-29 04:23:60.00-03 (6 rows) Thanks in advance. ----- Original Message ----- From: "Jean-Luc Lachance" <jllachan@nsd.ca> To: "Vilson farias" <vilson.farias@digitro.com.br> Cc: <pgsql-admin@postgresql.org> Sent: Thursday, August 29, 2002 5:16 PM Subject: Re: [ADMIN] Some timestamp problems > I think there is plenty of time to address the Y10K problem... :) > > JLL > > > Vilson farias wrote: > > > > Greetings, > > > > I found some strange behaviour in 7.1.2. Is it the same thing on 7.2.2 ? > > > > I don't know if it's a psql bug, but I would like to report it anyway > > > > bxs=# \d test2 > > Table "test2" > > Attribute | Type | Modifier > > ------------------+--------------------------+---------- > > x | integer | > > apagar_menor_que | timestamp with time zone | > > > > bxs=# insert into test2 values(1, '2111-10-01 04:23:59.999'); > > INSERT 259672 1 > > bxs=# insert into test2 values(2, '20001-10-01 04:23:50'); > > INSERT 259673 1 > > bxs=# insert into test2 values(3, '20001-10-01 04:23:59'); > > INSERT 259674 1 > > bxs=# insert into test2 values(4, '20001-10-01 04:23:59.99'); > > INSERT 259675 1 > > bxs=# insert into test2 values(5, '20001-10-01 04:23:59.999'); > > INSERT 259676 1 > > bxs=# insert into test2 values(6, '20001-10-01 04:23:59.998'); > > INSERT 259677 1 > > bxs=# SELECT * FROM test2; > > x | apagar_menor_que > > ---+------------------------ > > 1 | 2111-10-01 04:23:60.00 > > 2 | 20001-10-01 04:2350 > > 3 | 20001-10-01 04:2359 > > 4 | 20001-10-01 04:2359.99 > > 5 | 20001-10-01 04:2360.00 > > 6 | 20001-10-01 04:2360.00 > > (6 rows) > > > > bxs=# > > > > Best regards from Brazil, > > > > -------------------------------------------------------------------------- -- > > ---- > > José Vilson de Mello de Farias > > Software Engineer > > > > Dígitro Tecnologia Ltda - www.digitro.com.br > > APC - Customer Oriented Applications > > E-mail: vilson.farias@digitro.com.br > > Tel.: +55 48 281 7158 > > ICQ 11866179 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly >
I agree with you . My first reply was just in jest. :) Vilson farias wrote: > > But there are two kind of problems there. First related with big date > values. ok, bot big deal yet :), but the second is taking my attention : if > you put a time with 995 to 999 miliseconds, it's represented as 60seconds > instead of 59seconds and 995 to 999 miliseconds. I don't think it's alright, > because I've never seen a datetime with 60seconds value (60 seconds is > always the next minute, in my conception). I would like some light here, is > it just a psql problem to show datetime values or a internal datetime > storage problem? > > New example: > > bxs=# insert into test2 values(1, '2002-08-29 04:23:59.994'); > INSERT 259868 1 > bxs=# insert into test2 values(2, '2002-08-29 04:23:59.995'); > INSERT 259869 1 > bxs=# insert into test2 values(2, '2002-08-29 04:23:59.996'); > INSERT 259870 1 > bxs=# insert into test2 values(3, '2002-08-29 04:23:59.997'); > INSERT 259871 1 > bxs=# insert into test2 values(4, '2002-08-29 04:23:59.998'); > INSERT 259872 1 > bxs=# insert into test2 values(5, '2002-08-29 04:23:59.999'); > INSERT 259873 1 > bxs=# select * from test2; > x | apagar_menor_que > ---+--------------------------- > 1 | 2002-08-29 04:23:59.99-03 > 2 | 2002-08-29 04:23:59.99-03 > 2 | 2002-08-29 04:23:60.00-03 > 3 | 2002-08-29 04:23:60.00-03 > 4 | 2002-08-29 04:23:60.00-03 > 5 | 2002-08-29 04:23:60.00-03 > (6 rows) > > Thanks in advance. > > ----- Original Message ----- > From: "Jean-Luc Lachance" <jllachan@nsd.ca> > To: "Vilson farias" <vilson.farias@digitro.com.br> > Cc: <pgsql-admin@postgresql.org> > Sent: Thursday, August 29, 2002 5:16 PM > Subject: Re: [ADMIN] Some timestamp problems > > > I think there is plenty of time to address the Y10K problem... :) > > > > JLL > > > > > > Vilson farias wrote: > > > > > > Greetings, > > > > > > I found some strange behaviour in 7.1.2. Is it the same thing on 7.2.2 > ? > > > > > > I don't know if it's a psql bug, but I would like to report it anyway > > > > > > bxs=# \d test2 > > > Table "test2" > > > Attribute | Type | Modifier > > > ------------------+--------------------------+---------- > > > x | integer | > > > apagar_menor_que | timestamp with time zone | > > > > > > bxs=# insert into test2 values(1, '2111-10-01 04:23:59.999'); > > > INSERT 259672 1 > > > bxs=# insert into test2 values(2, '20001-10-01 04:23:50'); > > > INSERT 259673 1 > > > bxs=# insert into test2 values(3, '20001-10-01 04:23:59'); > > > INSERT 259674 1 > > > bxs=# insert into test2 values(4, '20001-10-01 04:23:59.99'); > > > INSERT 259675 1 > > > bxs=# insert into test2 values(5, '20001-10-01 04:23:59.999'); > > > INSERT 259676 1 > > > bxs=# insert into test2 values(6, '20001-10-01 04:23:59.998'); > > > INSERT 259677 1 > > > bxs=# SELECT * FROM test2; > > > x | apagar_menor_que > > > ---+------------------------ > > > 1 | 2111-10-01 04:23:60.00 > > > 2 | 20001-10-01 04:2350 > > > 3 | 20001-10-01 04:2359 > > > 4 | 20001-10-01 04:2359.99 > > > 5 | 20001-10-01 04:2360.00 > > > 6 | 20001-10-01 04:2360.00 > > > (6 rows) > > > > > > bxs=# > > > > > > Best regards from Brazil, > > > > > > > -------------------------------------------------------------------------- > -- > > > ---- > > > José Vilson de Mello de Farias > > > Software Engineer > > > > > > Dígitro Tecnologia Ltda - www.digitro.com.br > > > APC - Customer Oriented Applications > > > E-mail: vilson.farias@digitro.com.br > > > Tel.: +55 48 281 7158 > > > ICQ 11866179 > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that your > > > message can get through to the mailing list cleanly > >
On Thu, Aug 29, 2002 at 17:59:15 -0300, Vilson farias <vilson.farias@digitro.com.br> wrote: > But there are two kind of problems there. First related with big date > values. ok, bot big deal yet :), but the second is taking my attention : if > you put a time with 995 to 999 miliseconds, it's represented as 60seconds > instead of 59seconds and 995 to 999 miliseconds. I don't think it's alright, > because I've never seen a datetime with 60seconds value (60 seconds is > always the next minute, in my conception). I would like some light here, is > it just a psql problem to show datetime values or a internal datetime > storage problem? Note that if you are using UTC there are leap seconds stuck in or removed from time to time. So you can have a 61st (and I think even a 62nd) second in a given minute.