Re: JDBC, Timestamps, and Fractions of a Second - Mailing list pgsql-interfaces
From | Jim Caley |
---|---|
Subject | Re: JDBC, Timestamps, and Fractions of a Second |
Date | |
Msg-id | 3948EC2D.8CB627F8@chesco.com Whole thread Raw |
In response to | RE: JDBC, Timestamps, and Fractions of a Second (Peter Mount <petermount@it.maidstone.gov.uk>) |
List | pgsql-interfaces |
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());
pgsql-interfaces by date: