Re: JDBC, Timestamps, and Fractions of a Second

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