pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ?
4+ messages / 3 participants
[nested] [flat]
* How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ?
@ 2003-03-25 14:38 Liang Luo <liang.luo@convergia.net>
2003-03-26 14:06 ` Re: How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Liang Luo @ 2003-03-25 14:38 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ?
2003-03-25 14:38 How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ? Liang Luo <liang.luo@convergia.net>
@ 2003-03-26 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-28 15:57 ` Re: How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ? Liang Luo <liang.luo@convergia.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-03-26 14:06 UTC (permalink / raw)
To: liang.luo@convergia.net; +Cc: pgsql-sql
"Liang Luo" <liang.luo@convergia.net> writes:
> 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.
This looks like an old bug. What Postgres version are you using, and
on what platform?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ?
2003-03-25 14:38 How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ? Liang Luo <liang.luo@convergia.net>
2003-03-26 14:06 ` Re: How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-28 15:57 ` Liang Luo <liang.luo@convergia.net>
0 siblings, 0 replies; 4+ messages in thread
From: Liang Luo @ 2003-03-28 15:57 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
My version is 7.1, and I also have another version 7.2 but this problem does
not happen on version 7.2. Now I want to dump these data out from version
7.1 and upgrade it, and then load these data into version 7.2
Thanks,
Liang
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, March 26, 2003 9:07 AM
To: liang.luo@convergia.net
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] How to show timestamp with milliseconds(3 digits) in
Select clause in Ver7.1 ?
"Liang Luo" <liang.luo@convergia.net> writes:
> 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.
This looks like an old bug. What Postgres version are you using, and
on what platform?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to show timestamp with milliseconds(3 digits) in Select
@ 2003-03-26 09:16 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 4+ messages in thread
From: Christoph Haller @ 2003-03-26 09:16 UTC (permalink / raw)
To: pgsql-sql; +Cc: liang.luo@convergia.net
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-28 15:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-25 14:38 How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ? Liang Luo <liang.luo@convergia.net>
2003-03-26 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-28 15:57 ` Liang Luo <liang.luo@convergia.net>
2003-03-26 09:16 Re: How to show timestamp with milliseconds(3 digits) in Select Christoph Haller <ch@rodos.fzk.de>
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