Re: More Timestamp issues...

> "Skidmore, Walt" wrote:
>
> I have yet another timestamp issue that I'm not sure how to resolve... I'm
> using Postgres 7.0.2. For some reason, I'm getting the following error:
>
> Bad Timestamp Format at 19 in 2001-03-15 23:56:18.01+00

There have been some threads about this in the past, but in my experience
searching the archives can be hit and miss. So rather than refer you there
here's part of one of the threads, including a patch for 7.0.2, that deals with
it.

Regards,
Jim
--

Subject:
Re: [INTERFACES] RE: JDBC Timestamp Problem
Date:
Tue, 12 Dec 2000 09:40:12 -0500
From:
Jim Caley <caley(at)chesco(dot)com>
To:
Peter Mount <petermount(at)maidstone(dot)gov(dot)uk>
CC:
"'pgsql-interfaces(at)postgresql(dot)org'"
<pgsql-interfaces(at)postgresql(dot)org>
References:
1

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(at)maidstone(dot)gov(dot)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(at)madweed(dot)ncsc(dot)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(at)chesco(dot)com>
To:
pgsql-interfaces(at)postgresql(dot)org
CC:
patches(at)postgres(dot)retep(dot)org(dot)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(at)postgres(dot)retep(dot)org(dot)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(at)chesco(dot)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