Re: Problems storing timestamps with calendars

Martin/Kris

We've found the same problem with the postgres driver - the versions of
setTimestamp() and getTimestamp() taking Calendars adjust the timezone in
the wrong direction. In our case, this is an issue because we keep our
database in GMT, but the systems accessing it don't all use prepared
statements (so the bug isn't self-cancelling).

The JDBC API for PreparedStatement.setTimestamp() (and
ResultSet.getTimestamp()) is not at all clear about the semantics of the
call - are we:

1. Converting the timestamp into the calendar's timezone when updating
the database (as assumed by Oracle, MSSQL, DB2 etc.), or
2. Assuming that the timestamp was created using the wrong timezone, so
reversing the timezone calculation (as assumed by postgres).

I've patched this (just hacked really) in AbstractJdbc2Statement - see diff
below signature.

The results below are from a script run in the Australia/Brisbane timezone
(GMT + 10), on postgres, MSSQL, oracle and postgres (with the patched
driver). The test script:

* Inserts a row using setTimestamp(int, Timestamp), then retrieves the
row using getTimestamp(int), followed by getTimestamp(int, GMT calendar)
* Inserts a row using setTimestamp(int, Timestamp, GMT calendar), then
retrieves it using the 2 methods above.

The results show that the unmodified postgres driver *adds* 10 hours in
setTimestamp() for GMT, while the other 2 drivers (and my hacked version)
subtract 10 hours as one would expect.

Even though the JDBC spec isn't clear, I see a benefit in the postgres JDBC
drivers using the same interpretation as all the other drivers tested.

If people agree I'm happy to submit a proper patch. Also, please let me
know if you'd like a copy of my test script.

Cheers ............................ JD

John Dickson
Chief Architect

Dialect Solutions Group
D> +61 7 3224 9806
F> +61 7 3210 2566

jdickson at dialectsolutions dot com
www.dialectsolutions.com

Diff of patched driver:

---
/winc/Java/postgresql-jdbc-8.0-312.src/org/postgresql/jdbc2/AbstractJdbc2Sta
tement.java 2005-06-09 02:21:01.000000000 +1000
+++ src/org/postgresql/jdbc2/AbstractJdbc2Statement.java 2005-07-28
15:35:23.951250000 +1000
@@ -2894,7 +2894,12 @@
int caloffset = cal.getTimeZone().getRawOffset();
if (cal.getTimeZone().inDaylightTime(t))
millis += 60 * 60 * 1000;
- caloffset = (Add) ? (caloffset - localoffset) : -1 * (caloffset -
localoffset);
+
+ // *** JD ***
+ // offset calc should be reversed
+ //caloffset = (Add) ? (caloffset - localoffset) : -1 * (caloffset -
localoffset);
+ caloffset = (!Add) ? (caloffset - localoffset) : -1 * (caloffset -
localoffset);
+
java.util.Date tmpDate = new java.util.Date();
tmpDate.setTime(millis - caloffset);
cal.setTime(tmpDate);

Results from test script:
============================================
Testing postgres - please enter password
Inserting row with timestamp 2005-07-28 15:38:47.498 and calendar None
Timestamp retrieved for calendar None
2005-07-28 15:38:47.498
Timestamp retrieved for calendar None
2005-07-28 15:38:47.498
Inserting row with timestamp 2005-07-28 15:38:47.56 and calendar GMT
Timestamp retrieved for calendar None
2005-07-29 01:38:47.0
Timestamp retrieved for calendar GMT
2005-07-28 15:38:47.0
============================================
Testing mssql - please enter password
Inserting row with timestamp 2005-07-28 15:38:51.373 and calendar None
Timestamp retrieved for calendar None
2005-07-28 15:38:51.373
Timestamp retrieved for calendar None
2005-07-28 15:38:51.373
Inserting row with timestamp 2005-07-28 15:38:51.404 and calendar GMT
Timestamp retrieved for calendar None
2005-07-28 05:38:51.403
Timestamp retrieved for calendar GMT
2005-07-28 15:38:51.403
============================================
Testing oracle - please enter password
Inserting row with timestamp 2005-07-28 15:38:54.81 and calendar None
Timestamp retrieved for calendar None
2005-07-28 15:38:54.81
Timestamp retrieved for calendar None
2005-07-28 15:38:54.81
Inserting row with timestamp 2005-07-28 15:38:54.888 and calendar GMT
Timestamp retrieved for calendar None
2005-07-28 05:38:54.888
Timestamp retrieved for calendar GMT
2005-07-28 15:38:54.0
============================================
Testing postgres with patched jar - please enter password
Inserting row with timestamp 2005-07-28 15:38:58.763 and calendar None
Timestamp retrieved for calendar None
2005-07-28 15:38:58.763
Timestamp retrieved for calendar None
2005-07-28 15:38:58.763
Inserting row with timestamp 2005-07-28 15:38:58.826 and calendar GMT
Timestamp retrieved for calendar None
2005-07-28 05:38:58.0
Timestamp retrieved for calendar GMT
2005-07-28 15:38:58.0

Martin wrote
> unfortunately, this fix doesn't solve the problem.
> As far as I understand, this topic isn't that much complicated:

Kris Jurka wrote:

On Wed, 27 Apr 2005, Martin Keller wrote:

We have some trouble with storing timestamps via
PreparedStatement.setTimestamp(int, Timestamp, Calendar) into a timestamp
column without timezone.
It looks like that the timestamp values are adjusted in the wrong direction
if the timezone of the calendar is not the default timezone of the java vm.

Are we doing anything wrong or is this a bug?

Something is definitely wrong here. At first glance it doesn't look like
it's related to the vm timezone, but instead
AbstractJdbc2Statement.changeTime should be respecting Add for the DST
offset, but I'm not quite sure of that. It gets complicated when you've
got to consider four different possible timezones (server, vm, timestamp,
calendar). Does this jar file fix things for you?

http://www.ejurka.com/pgsql/jars/mk/ <http://www.ejurka.com/pgsql/jars/mk/>

Kris Jurka

IMPORTANT NOTICE
-------------------------------
Confidentiality Note: The information contained in this email and any
attachments is confidential and/or privileged. This email and any
attachments are intended to be read only by the person named above. If the
reader of this email and any attachments is not the intended recipient, or a
representative of the intended recipient, you are hereby notified that any
review, dissemination or copying of this email and any attachments is
prohibited. If you have received this email and any attachments in error,
please notify the sender by email, telephone or fax and return it to the
sender