pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
JDBC, Timestamp and getting microseconds 2+ messages / 2 participants [nested] [flat]
* JDBC, Timestamp and getting microseconds @ 2000-12-08 03:17 Philip Crotwell <crotwell@seis.sc.edu> 0 siblings, 1 reply; 2+ messages in thread From: Philip Crotwell @ 2000-12-08 03:17 UTC (permalink / raw) To: pgsql-interfaces; +Cc: Philip Crotwell <crotwell@seis.sc.edu> Hi 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. 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. 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)? And what does 1 microsec / 14 digits mean? thanks, Philip ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: JDBC, Timestamp and getting microseconds @ 2000-12-13 05:00 Thomas Lockhart <lockhart@alumni.caltech.edu> parent: Philip Crotwell <crotwell@seis.sc.edu> 0 siblings, 0 replies; 2+ messages in thread From: Thomas Lockhart @ 2000-12-13 05:00 UTC (permalink / raw) To: Philip Crotwell <crotwell@seis.sc.edu>; +Cc: pgsql-interfaces > 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-12-13 05:00 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-12-08 03:17 JDBC, Timestamp and getting microseconds Philip Crotwell <crotwell@seis.sc.edu> 2000-12-13 05:00 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
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