Thread: JDBC Timestamp Problem
Hello, I have a problem with timestamps and JDBC-Postgresql It seems that the JDBC-driver doesn't parse all representations of timestamps. I calculate my own Timestamp and insert it into a table. The field seems to contain (with psql SELECT): 2000-12-06 00:05:39.57+01 When i try to use a res.getTimestamp(i) i get an unparseable Timestamp exception at position 19 When i use a now() value the field contains (with psql SELECT): 2000-12-07 11:42:36+01 I get the impression that either there is a representation issue with postgresql or there is a small problem with the JDBCdriver or i'm doing something wrong :-) Greetz, Yves De Muyter
> I have a problem with timestamps and JDBC-Postgresql [snip] > I get the impression that either there is a representation issue with > postgresql or there is a small problem with the JDBC driver or i'm > doing something wrong :-) This is fixed in the latest CVS source for the JDBC driver (see retep.org.uk, which currently appears to be down). Failing that, if you look in the archive of this list you'll find a patch to fix this problem. Michael Stephenson
What version of the driver are you using? This particular bug should have gone long ago (~7.0) Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: yves@asua.vlaanderen.net [mailto:yves@asua.vlaanderen.net] > Sent: Saturday, December 09, 2000 1:44 PM > To: pgsql-interfaces@postgresql.org > Subject: [INTERFACES] JDBC Timestamp Problem > > > Hello, > > I have a problem with timestamps and JDBC-Postgresql > > It seems that the JDBC-driver doesn't parse all > representations of timestamps. > I calculate my own Timestamp and insert it into a table. > The field seems to contain (with psql SELECT): > > 2000-12-06 00:05:39.57+01 > > When i try to use a res.getTimestamp(i) i get an unparseable > Timestamp exception at position 19 > > When i use a now() value the field contains (with psql SELECT): > > 2000-12-07 11:42:36+01 > > I get the impression that either there is a representation > issue with postgresql or there is a small problem with the > JDBC driver or i'm doing something wrong :-) > > Greetz, > > Yves De Muyter >
retep.org.uk is down as I'm moving it from demon to hub (finally, it's taken a few weeks longer than it should have done). The DNS should be pointing to hub now, but it may take about 24hrs for the world to pick up the changes. On a related note, the petermount@maidstone.gov.uk email address will go at the end of next week (22nd December), so the retep.org.uk address should be the only one from then on. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Michael Stephenson [mailto:mstephenson@tirin.openworld.co.uk] > Sent: Monday, December 11, 2000 10:30 AM > To: pgsql-interfaces@postgresql.org > Subject: Re: [INTERFACES] JDBC Timestamp Problem > > > > I have a problem with timestamps and JDBC-Postgresql > [snip] > > I get the impression that either there is a representation > issue with > > postgresql or there is a small problem with the JDBC driver or i'm > > doing something wrong :-) > > This is fixed in the latest CVS source for the JDBC driver (see > retep.org.uk, which currently appears to be down). > > Failing that, if you look in the archive of this list you'll > find a patch > to fix this problem. > > Michael Stephenson >
I ran into a Timestamp problem a while back. The Postgres back end seems to return 2 digits for the milliseconds when the JDBC is expecting 3 digits (and 3 digits is correct). I am not sure if this has already been fixed or reported or even if it is the real problem at all. Does anyone know where I could checked to see if a patch is availalbe for this? Thanks, Ken
Yes, about 1-2 months ago ;-) The current CVS has the patch applied. As soon as I get the domain problems sorted, I'm going to tripple check Timestamp as I'd like to see the next release without the timestamp bug reappearing... Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Ken Kachnowich [mailto:khkachn@madweed.ncsc.mil] > Sent: Monday, December 11, 2000 5:30 PM > To: pgsql-interfaces > Subject: [INTERFACES] RE: JDBC Timestamp Problem > > > I ran into a Timestamp problem a while back. The Postgres back end > seems to return 2 digits for the milliseconds when the JDBC > is expecting > > 3 digits (and 3 digits is correct). > > I am not sure if this has already been fixed or reported or even if it > is the > real problem at all. > > Does anyone know where I could checked to see if a patch is availalbe > for this? > > Thanks, > > Ken > > >
Although I haven't installed 7.0.3 personally, it sounds like it fixes the
problem.
However, since there have been a couple mentions of the patch -- which I've been
using successfully for months now in my installation* -- in this thread, I
thought I'd post it again.
Please see my original message below, which contains the
patch. (BTW, since the time I posted that, I realized I was not clear
which ResultSet.java file needed to be patched: you patch the one in
.../org/postgresql/jdbc2, not the one in .../org/postgresql. You would
figure this out quickly anyway, but just to save you some time... :)
I'm assuming here that you're working with jdbc2, I haven't looked at
jdbc1.) Also, please note that there are a couple of long lines which have
"wrapped around" in past e-mails which you may need to fix. They both contain
"new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz")".
Regards,
Jim
*Please note that this patch is only intended to fix the *specific* problem
addressed in the current thread. It sounds like Peter has gone beyond that with
his work. Also, I originally applied the patch to 7.0.1, and now also to 7.0.2,
which still had the problem (until I applied the patch, that is).
--
Peter Mount wrote:
>
> Yes, about 1-2 months ago ;-) The current CVS has the patch applied.
>
> As soon as I get the domain problems sorted, I'm going to tripple check
> Timestamp as I'd like to see the next release without the timestamp bug
> reappearing...
>
> Peter
>
> --
> Peter Mount
> Enterprise Support Officer, Maidstone Borough Council
> Email: petermount@maidstone.gov.uk
> WWW: http://www.maidstone.gov.uk
> All views expressed within this email are not the views of Maidstone Borough
> Council
>
> > -----Original Message-----
> > From: Ken Kachnowich [mailto:khkachn@madweed.ncsc.mil]
> > Sent: Monday, December 11, 2000 5:30 PM
> > To: pgsql-interfaces
> > Subject: [INTERFACES] RE: JDBC Timestamp Problem
> >
> >
> > I ran into a Timestamp problem a while back. The Postgres back end
> > seems to return 2 digits for the milliseconds when the JDBC
> > is expecting
> >
> > 3 digits (and 3 digits is correct).
> >
> > I am not sure if this has already been fixed or reported or even if it
> > is the
> > real problem at all.
> >
> > Does anyone know where I could checked to see if a patch is availalbe
> > for this?
> >
> > Thanks,
> >
> > Ken
> >
> >
> >
BEGIN JIM CALEY'S PREVIOUS POST
-------------------------------
Subject: [INTERFACES] JDBC, Timestamps, and Fractions of a Second Date: Wed, 14 Jun 2000 17:27:02 -0400
From: Jim Caley <caley@chesco.com> To: pgsql-interfaces@postgresql.org CC:
patches@postgres.retep.org.uk
I'm using the JDBC2 driver in the PostgreSQL 7.0.1 distribution to both
INSERT and SELECT (the same) records with timestamp columns. The
ResultSet.getTimestamp method is choking, because it's looking for a
"yyyy-MM-dd HH:mm:sszzz" format, while the default in 7.0 now seems to
be "yyyy-MM-dd HH:mm:ss.SSzzz", where "SS" is centiseconds. (The Java 2
SDK javadoc for SimpleDateFormat actually defines 'S' to represent a
millisecond digit in a time pattern string, but I'm using it here for
centiseconds since they define no character for that. :-/ )
Here's part of the stack trace:
Bad Timestamp Format at 19 in 2000-06-14 15:37:11.67-04 at
org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:447) at
org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:595)
Aleksey Demakov wrote about a similar problem in 1998, (see
http://www.postgresql.org/mhonarc/pgsql-interfaces/1998-10/msg00183.html),
and he rewrote the code to account for milliseconds.
Likewise, I've rewritten the current code to account for both the "no
fractions" format and the "centiseconds" format. The patch is below.
This is solving my immediate problem, but I don't know what other
formats may need to be taken into consideration to make the patch an
acceptably robust solution.
I'd appreciate any comments (e.g. could this patch -- or a more robust
version -- be applied to the next version of the driver? -- I am CCing
this to patches@postgres.retep.org.uk).
(As an aside, in the way of trivia, a search of
http://www.dictionary.com turned up no legitimate word "centiseconds."
However, a search on Google confirmed that, correctly or not, others do
use it. :)
Platform info: -Red Hat Linux 6.2 -Blackdown JDK 1.2.2 RC4
Regards,
Jim Caley
E-mail: caley@chesco.com
--
*** ResultSet.java.orig Fri May 12 16:54:22 2000
--- ResultSet.java Tue Jun 13 16:46:21 2000
***************
*** 439,445 **** if(s==null) return null;
! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz"); try { return new
Timestamp(df.parse(s).getTime());
--- 439,447 ---- if(s==null) return null;
! SimpleDateFormat df = (s.charAt(19) == '.') ?
! new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz") :
! new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz"); try { return new
Timestamp(df.parse(s).getTime());
---------------------------
END JIM'S POST
> Yes, about 1-2 months ago ;-) The current CVS has the patch applied.
> As soon as I get the domain problems sorted, I'm going to tripple check
> Timestamp as I'd like to see the next release without the timestamp bug
> reappearing...
Do you want to talk about what PostgreSQL *should* return for timestamp
values? Currently, it rounds to two digits if there is a non-zero
fractional part, and omits the fractional part otherwise.
Both features are there for readability and to eliminate the possibility
of accumulated rounding errors introducing "lots 'o nines" in the
output. But we *could* make it variable length or do more checking and
rounding in a different way. And we *could* at least have a SET
key=value parameter which you could use to guarantee a format for a
session.
The fact that JDBC has troubles with the current scheme means that
others are probably having trouble too...
- Thomas
-- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Thomas Lockhart [mailto:lockhart@alumni.caltech.edu] > Sent: Tuesday, December 12, 2000 3:28 PM > To: Peter Mount > Cc: 'pgsql-interfaces@postgresql.org' > Subject: Re: [INTERFACES] RE: JDBC Timestamp Problem > > > > Yes, about 1-2 months ago ;-) The current CVS has the patch applied. > > As soon as I get the domain problems sorted, I'm going to > tripple check > > Timestamp as I'd like to see the next release without the > timestamp bug > > reappearing... > > Do you want to talk about what PostgreSQL *should* return for > timestamp values? Currently, it rounds to two digits if there > is a non-zero fractional part, and omits the fractional part > otherwise. It might be an idea... getTimestamp() seemed to break when 7.0 was released. I've had so many different methods sent to me about how to check for it, I've so far ended up picking the simplest of them. If we can sort out what Timestamp returns then may be we can get rid of this problem for good. Currently, JDBC switches datestyle to ISO so that all the date routines have the same format. One suggestion was to switch from ISO to Postgres but I'm worried if anyone else out there has code that relies on it running with ISO, in which case the change would break them. > Both features are there for readability and to eliminate the > possibility > of accumulated rounding errors introducing "lots 'o nines" in the > output. But we *could* make it variable length or do more checking and > rounding in a different way. And we *could* at least have a SET > key=value parameter which you could use to guarantee a format for a > session. > > The fact that JDBC has troubles with the current scheme means that > others are probably having trouble too... I think the other main one that would be affected would be ODBC as both API's tend to mirror what they do, and I suspect they have an equivalent of getTimestamp(); Peter
This does seem to be a thorny problem. It seems we need an answer that does the following: follows the ISO date style maintains the Postgres milliseconds returns valid records when a select is done based on a Timestamp` Best 2 out of 3? Return milliseconds from the Postgres back end. Ken