Message-ID: <3E816FF0.77172AE7@rodos.fzk.de>
Date: Wed, 26 Mar 2003 10:16:32 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: liang.luo@convergia.net
Subject: Re: How to show timestamp with milliseconds(3 digits) in Select 
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> This is the query and result:
>
> select h323_setup_time from pv_legs_new where h323_setup_time =
'2003-01-01
> 00:43:59.996-05';
>
>       h323_setup_time
> ---------------------------
>  2003-01-01 00:43:60.00-05
>
> Actually, the real data of second and millisecond is 59.996-05, but it
shows
> 60.00. I failed to load it when I use this result in other query.
>
I'm using 7.3 and it works as expected:
create table tstamptest ( tstampcol timestamp );
insert into tstamptest values ( '2003-01-01 00:43:59.996-05' );
select * from tstamptest ;
        tstampcol
-------------------------
 2003-01-01 00:43:59.996

The 7.1 doc does not say anything about microsecond representation nor
storage,
7.3 has
Note: When timestamp values are stored as double precision
floating-point numbers (currently the default), the
effective limit of precision may be less than 6, since timestamp values
are stored as seconds since 2000-01-01.
Microsecond precision is achieved for dates within a few years of
2000-01-01, but the precision degrades for dates
further away. When timestamps are stored as eight-byte integers (a
compile-time option), microsecond precision is
available over the full range of values.

So I can only offer the standard advice: upgrade to 7.3.

Regards, Christoph