From: Thomas Lockhart <lockhart@alumni.caltech.edu>
To: Philip Crotwell <crotwell@seis.sc.edu>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: JDBC, Timestamp and getting microseconds
Date: Wed, 13 Dec 2000 05:00:04 +0000
Message-ID: <3A370254.44C55482@alumni.caltech.edu> (raw)
References: <Pine.GSO.4.10.10012072146510.3465-100000@tigger.seis.sc.edu>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: lockhart@alumni.caltech.edu, crotwell@seis.sc.edu
Subject: Re: JDBC, Timestamp and getting microseconds
In-Reply-To: <3A370254.44C55482@alumni.caltech.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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