Message-ID: <3A370254.44C55482@alumni.caltech.edu>
Date: Wed, 13 Dec 2000 05:00:04 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
Organization: Yes
MIME-Version: 1.0
To: Philip Crotwell <crotwell@seis.sc.edu>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: JDBC, Timestamp and getting microseconds
References: <Pine.GSO.4.10.10012072146510.3465-100000@tigger.seis.sc.edu>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> I have an application storing seismology data that needs to store
> Timestamps (or something similar) with at least tenth of millisecond
> precision. The docs for postgres 7.0 say that Timestamp has "1 microsec /
> 14 digits" resolution, and the JDBC Timestamp has room for nanoseconds, so
> I thought I was fine.

Yup.

> But from looking at the jdbc ResultSet code, it seems that everything is
> chopped off at hundredths of a second??? I also looked into the code for
> ResultSet.getTIme, thinking that I could store the date and time
> separately, but it seems to chop it off at even seconds.

That is an issue with the PostgreSQL timestamp *output* function(s) only
(zero or two decimal places are hardcoded in :(. We've been discussing
how to move beyond this, and you *could* build your own version of code
which prints a large number of decimal places. Not sure how that
interacts with the JDBC driver. Peter?

> I have played a little bit with datestyle and psql and I suspect that this
> may not directly be a jdbc problem as they seem to chop at hundredths as
> well. So, is there any way to get a higher precision Timestamp (or
> equivalent) in and out of postgres with jdbc without loosing resolution or
> resorting to putting in my own format (maybe long micros since 1970)?

Not sure about the JDBC layer. The backend uses a "%05.2f" or "%02.0f"
format statement, depending on whether there are fractional seconds.
Look in src/backend/utils/adt/datetime.c for the routine
EncodeDateTime() for details.

> And what does 1 microsec / 14 digits mean?

The value is stored as a double, calculated as the number of seconds
since 2000-01-01. So you have >50 bits of precision if your times are
around now, with fewer of those bits in the fractional part if you are
far away from Y2K.

                       - Thomas