Timestamp Resolution in Postgres
Hi All,
Apologies if I posted to the wrong groups.
I am trying to use the timestamp column type in postgres. It appears that
postgres is rounding
the milliseconds to the nearest 10ms. I'm running on Solaris 8 - and my
app is written in
java which is returning dates with a 1ms accuracy. Is there a setting
somewhere to adjust
the resolution of the timestamp field? I haven't seen anything in the
documentation. Enclosed below
is a simple example which exhibits the behavior.
Thanks for any help!
Mike
Let's say I have a table called person:
create table person (
OBJECTID VARCHAR( 56),
BIRTHDAY TIMESTAMP,
NAME VARCHAR(256)
);
If I run the following command in psql:
update table person set birthday = '2001-05-03 11:12:56.343' where objectid
= '34';
followed by
select * from person where objectid = '34';
I get :
2001-05-03 11:12:56.34-07
update table person set birthday = '2001-05-03 11:12:56.343' where
objectid
= '34';
followed by
select * from person where objectid = '34';
I get :
2001-05-03 11:12:56.34-07
This is a frustration that I also ran into, but there's no clean way to
handle it, especialy when using JDBC which takes the default String format
for a date, and therefore the thousandth of a second number is always
dropped. It's odd, though, since the database itself claims to support down
to millionths or nanos or some such thing. But I've not heard any mention
about how to change the default format to include more digits.
The alternatives appear to be: 1) don't worry about unless that extra
"resolution" really matters -- you know the system clock is not that
accurate anyway; 2) store it in an INT8/long...
David
You can edit the file src/backend/utils/adt/datetime.c and recompile.
David Wall wrote:
update table person set birthday = '2001-05-03 11:12:56.343' where
objectid
= '34';
followed by
select * from person where objectid = '34';
I get :
2001-05-03 11:12:56.34-07
This is a frustration that I also ran into, but there's no clean way to
handle it, especialy when using JDBC which takes the default String format
for a date, and therefore the thousandth of a second number is always
dropped. It's odd, though, since the database itself claims to support down
to millionths or nanos or some such thing. But I've not heard any mention
about how to change the default format to include more digits.The alternatives appear to be: 1) don't worry about unless that extra
"resolution" really matters -- you know the system clock is not that
accurate anyway; 2) store it in an INT8/long...David
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com