Revised Patch for JDBC timestamp problems
Attached is a revised patch that removes the static SimpleDateFormat
objects that Thomas pointed out might be a problem.
thanks,
--Barry
PS. Is this the correct way to submit patches by sending to
pgsql-patches? It appeared from some of the comments on my last
submission that there is some other approval/review process that I
should go through first, but I don't see that documented anywhere, so if
I am doing something wrong, please forgive me, and point me in the right
direction.
PPS. I have included and updated the comments from the original patch
request to reflect the changes made in this revised patch.
Show quoted text
Attached is a set of patches for a couple of bugs dealing with
timestamps in JDBC.Bug#1) Incorrect timestamp stored in DB if client timezone different
than DB.The buggy implementation of setTimestamp() in PreparedStatement simply
used the toString() method of the java.sql.Timestamp object to convert
to a string to send to the database. The format of this is yyyy-MM-dd
hh:mm:ss.SSS which doesn't include any timezone information. Therefore
the DB assumes its timezone since none is specified. That is OK if the
timezone of the client and server are the same, however if they are
different the wrong timestamp is received by the server. For example if
the client is running in timezone GMT and wants to send the timestamp
for noon to a server running in PST (GMT-8 hours), then the server will
receive 2000-01-12 12:00:00.0 and interprete it as 2000-01-12
12:00:00-08 which is 2000-01-12 04:00:00 in GMT. The fix is to send a
format to the server that includes the timezone offset. For simplicity
sake the fix uses a SimpleDateFormat object with its timezone set to GMT
so that '+00' can be used as the timezone for postgresql. This is done
as SimpleDateFormat doesn't support formating timezones in the way
postgresql expects.Bug#2) Incorrect handling of partial seconds in getting timestamps from
the DBWhen the SimpleDateFormat object parses a string with a format like
yyyy-MM-dd hh:mm:ss.SS it expects the fractional seconds to be three
decimal places (time precision in java is miliseconds = three decimal
places). This seems like a bug in java to me, but it is unlikely to be
fixed anytime soon, so the postgresql code needed modification to
support the java behaviour. So for example a string of '2000-01-12
12:00:00.12-08' coming from the database was being converted to a
timestamp object with a value of 2000-01-12 12:00:00.012GMT-08:00. The
fix was to check for a '.' in the string and if one is found append on
an extra zero to the fractional seconds part.I also did some cleanup in ResultSet.getTimestamp(). This method has
had multiple patches applied some of which resulted in code that was no
longer needed. For example the ISO timestamp format that postgresql
uses specifies the timezone as an offset like '-08'. Code was added at
one point to convert the postgresql format to the java one which is
GMT-08:00, however the old code was left around which did nothing. So
there was code that looked for yyyy-MM-dd hh:mm:sszzzzzzzzz and
yyyy-MM-dd hh:mm:sszzz. This second format would never be encountered
because zzz (i.e. -08) would be converted into the former (also note
that the SimpleDateFormat object treats zzzzzzzzz and zzz the same, the
number of z's does not matter).There was another problem/fix mentioned on the email lists today by
mcannon@internet.com which is also fixed by this patch:Bug#3) Fractional seconds lost when getting timestamp from the DB
A patch by Jan Thomea handled the case of yyyy-MM-dd hh:mm:sszzzzzzzzz
but not the fractional seconds version yyyy-MM-dd hh:mm:ss.SSzzzzzzzzz.
The code is fixed to handle this case as well.
Attachments:
patch.difftext/plain; charset=UTF-8; name=patch.diffDownload+147-146
Attached is a revised patch that removes the static SimpleDateFormat
objects that Thomas pointed out might be a problem.
Thanks. I will apply it as soon as I can.
PS. Is this the correct way to submit patches by sending to
pgsql-patches? It appeared from some of the comments on my last
submission that there is some other approval/review process that I
should go through first, but I don't see that documented anywhere, so if
I am doing something wrong, please forgive me, and point me in the right
direction.
No, Barry, you are doing great. Seems we have an internal issue here
because I don't understand the java code, so I have trouble evaluating
it, and sometimes I am over-eager to get these jdbc fixes into the code.
Please, just keep doing what you are doing. The JDBC driver needs all
the help it can get.
PPS. I have included and updated the comments from the original patch
request to reflect the changes made in this revised patch.
Thanks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied after confirmation from Java users. Thanks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026