Thread: 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