From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: liang.luo@convergia.net
Subject: Re: How to show timestamp with milliseconds(3 digits) in Select
Date: Wed, 26 Mar 2003 10:16:32 +0100
Message-ID: <3E816FF0.77172AE7@rodos.fzk.de> (raw)
>
> 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, liang.luo@convergia.net
Subject: Re: How to show timestamp with milliseconds(3 digits) in Select
In-Reply-To: <3E816FF0.77172AE7@rodos.fzk.de>
* 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