Skip site navigation (1) Skip section navigation (2)

Re: JDBC, Timestamps, and Fractions of a Second

From: Nissim <nissim(at)nksystems(dot)com>
To: Peter Mount <petermount(at)it(dot)maidstone(dot)gov(dot)uk>
Cc: "'Jim Caley'" <caley(at)chesco(dot)com>, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: JDBC, Timestamps, and Fractions of a Second
Date: 2000-06-15 13:42:23
Message-ID: 3948DD3F.B0324DE7@nksystems.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
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(at)retep(dot)org(dot)uk(dot) 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(at)chesco(dot)com]
> Sent: Wednesday, June 14, 2000 10:27 PM
> To: pgsql-interfaces(at)postgresql(dot)org
> Cc: patches(at)postgres(dot)retep(dot)org(dot)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(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());

In response to

Responses

pgsql-interfaces by date

Next:From: Jim CaleyDate: 2000-06-15 14:46:05
Subject: Re: JDBC, Timestamps, and Fractions of a Second
Previous:From: Peter MountDate: 2000-06-15 07:07:03
Subject: RE: JDBC, Timestamps, and Fractions of a Second

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group