Re: 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...

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
>
>
>

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

> 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