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

Revised Patch for JDBC timestamp problems

From: Barry Lind <barry(at)xythos(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Revised Patch for JDBC timestamp problems
Date: 2001-01-13 23:49:04
Message-ID: 3A60E970.46B72421@xythos.com (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-jdbcpgsql-patches
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.

> 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 DB
> 
> When 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(at)internet(dot)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.

Attachment: patch.diff
Description: text/plain (10.1 KB)

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2001-01-14 03:08:08
Subject: Re: [PATCHES] Revised Patch for JDBC timestamp problems
Previous:From: The Hermit HackerDate: 2001-01-13 22:03:13
Subject: Re: [PATCHES] Patch for JDBC timestamp problems

pgsql-interfaces by date

Next:From: Bruce MomjianDate: 2001-01-14 03:08:08
Subject: Re: [PATCHES] Revised Patch for JDBC timestamp problems
Previous:From: The Hermit HackerDate: 2001-01-13 22:03:13
Subject: Re: [PATCHES] Patch for JDBC timestamp problems

pgsql-jdbc by date

Next:From: Bruce MomjianDate: 2001-01-14 03:08:08
Subject: Re: [PATCHES] Revised Patch for JDBC timestamp problems
Previous:From: The Hermit HackerDate: 2001-01-13 22:03:13
Subject: Re: [PATCHES] Patch for JDBC timestamp problems

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