Postgres Pro
Downloads
Home   >   mailing lists

Thread: Some timestamp problems

Some timestamp problems

From
"Vilson farias"
Date:
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

Re: Some timestamp problems

From
Jean-Luc Lachance
Date:
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

Re: Some timestamp problems

From
"Vilson farias"
Date:
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
>

Re: Some timestamp problems

From
Jean-Luc Lachance
Date:
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
> >

Re: Some timestamp problems

From
Bruno Wolff III
Date:
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.