Обсуждение: 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