Thread: JDBC, Timestamps, and Fractions of a Second

JDBC, Timestamps, and Fractions of a Second

From
Jim Caley
Date:
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());


RE: JDBC, Timestamps, and Fractions of a Second

From
Peter Mount
Date:
Two things: First, Timestamps in the JDBC driver have always had one problem
or another, but so far no patch that's been passed on to me has worked for
everyone - normally it's time zones that have problems.

I'll check today (as there's been two reports over night) and see how this
one works.

Second, the only valid retep.org.uk address is peter@retep.org.uk. The
others haven't existed for some time now. Also for the next few weeks, it
will be better to email me here than at home as I'm moving home next week so
it will take a while to get comms working again.

Peter

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council


-----Original Message-----
From: Jim Caley [mailto:caley@chesco.com]
Sent: Wednesday, June 14, 2000 10:27 PM
To: pgsql-interfaces@postgresql.org
Cc: patches@postgres.retep.org.uk
Subject: [INTERFACES] JDBC, Timestamps, and Fractions of a Second


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());


Re: JDBC, Timestamps, and Fractions of a Second

From
Nissim
Date:
I know I mentioned this before, and the suggestion wasn't looked upon
favorably on the pg-hackers list, but this problem stems from the fact
that the server returns the timestamp in a different format if the
number of "centiseconds" is 0 or other than 0.  Even if you insert with
the "centiseconds" value there, but it is zero (e.g. set timestamp_col =
'2000-06-03 11:47:47.00-04') when you select it out, it willl come out
without the .00  There is a whole bunch of code in
backend/utils/adt/datetime.c that formats the timestamp in this way.  I
think that the timestamp should be returned from the database in the
same format no matter whether it has millisecond or not.  Isn't that how
other databases work?
-Nissim


Peter Mount wrote:
> 
> Two things: First, Timestamps in the JDBC driver have always had one problem
> or another, but so far no patch that's been passed on to me has worked for
> everyone - normally it's time zones that have problems.
> 
> I'll check today (as there's been two reports over night) and see how this
> one works.
> 
> Second, the only valid retep.org.uk address is peter@retep.org.uk. The
> others haven't existed for some time now. Also for the next few weeks, it
> will be better to email me here than at home as I'm moving home next week so
> it will take a while to get comms working again.
> 
> Peter
> 
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough Council
> 
> -----Original Message-----
> From: Jim Caley [mailto:caley@chesco.com]
> Sent: Wednesday, June 14, 2000 10:27 PM
> To: pgsql-interfaces@postgresql.org
> Cc: patches@postgres.retep.org.uk
> Subject: [INTERFACES] JDBC, Timestamps, and Fractions of a Second
> 
> 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());


Re: JDBC, Timestamps, and Fractions of a Second

From
Jim Caley
Date:
Hmmm... I see what you mean.  I will point out that, as written, the
patch in my earlier message also handles timestamps without the
centiseconds nicely (in my environment, at least).  However, I realize
that this still doesn't directly address your question about what the
behavior of the database should be.

Regards,
Jim
--

Nissim wrote:
> 
> I know I mentioned this before, and the suggestion wasn't looked upon
> favorably on the pg-hackers list, but this problem stems from the fact
> that the server returns the timestamp in a different format if the
> number of "centiseconds" is 0 or other than 0.  Even if you insert with
> the "centiseconds" value there, but it is zero (e.g. set timestamp_col =
> '2000-06-03 11:47:47.00-04') when you select it out, it willl come out
> without the .00  There is a whole bunch of code in
> backend/utils/adt/datetime.c that formats the timestamp in this way.  I
> think that the timestamp should be returned from the database in the
> same format no matter whether it has millisecond or not.  Isn't that how
> other databases work?
> 
>         -Nissim
> 
> Peter Mount wrote:
> >
> > Two things: First, Timestamps in the JDBC driver have always had one problem
> > or another, but so far no patch that's been passed on to me has worked for
> > everyone - normally it's time zones that have problems.
> >
> > I'll check today (as there's been two reports over night) and see how this
> > one works.
> >
> > Second, the only valid retep.org.uk address is peter@retep.org.uk. The
> > others haven't existed for some time now. Also for the next few weeks, it
> > will be better to email me here than at home as I'm moving home next week so
> > it will take a while to get comms working again.
> >
> > Peter
> >
> > --
> > Peter Mount
> > Enterprise Support
> > Maidstone Borough Council
> > Any views stated are my own, and not those of Maidstone Borough Council
> >
> > -----Original Message-----
> > From: Jim Caley [mailto:caley@chesco.com]
> > Sent: Wednesday, June 14, 2000 10:27 PM
> > To: pgsql-interfaces@postgresql.org
> > Cc: patches@postgres.retep.org.uk
> > Subject: [INTERFACES] JDBC, Timestamps, and Fractions of a Second
> >
> > 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());