pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Timestamp Resolution in Postgres
3+ messages / 3 participants
[nested] [flat]

* Timestamp Resolution in Postgres
@ 2001-05-03 18:36  Michael Schroepfer <mike@raplix.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Schroepfer @ 2001-05-03 18:36 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql

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









^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Timestamp Resolution in Postgres
@ 2001-05-07 16:20  David Wall <d.wall@computer.org>
  parent: Michael Schroepfer <mike@raplix.com>
  0 siblings, 1 reply; 3+ messages in thread

From: David Wall @ 2001-05-07 16:20 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Timestamp Resolution in Postgres
@ 2001-05-07 19:21  Joseph Shraibman <jks@selectacast.net>
  parent: David Wall <d.wall@computer.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Joseph Shraibman @ 2001-05-07 19:21 UTC (permalink / raw)
  To: David Wall <d.wall@computer.org>; +Cc: pgsql-general@postgresql.org; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-05-07 19:21 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-03 18:36 Timestamp Resolution in Postgres Michael Schroepfer <mike@raplix.com>
2001-05-07 16:20 ` David Wall <d.wall@computer.org>
2001-05-07 19:21   ` Joseph Shraibman <jks@selectacast.net>

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