Re: Problems storing timestamps with calendars
| От | John Dickson |
|---|---|
| Тема | Re: Problems storing timestamps with calendars |
| Дата | |
| Msg-id | CF2BC135FB37D51197D400508BAF5217038CEA70@aubnmsx01.qsi.com.au обсуждение исходный текст |
| Ответ на | Problems storing timestamps with calendars (Martin Keller <martin.keller@unitedplanet.de>) |
| Ответы |
Re: Problems storing timestamps with calendars
(Oliver Jowett <oliver@opencloud.com>)
|
| Список | pgsql-jdbc |
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:
- Converting the timestamp into the calendar's timezone when updating the database (as assumed by Oracle, MSSQL, DB2 etc.), or
- 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
--- /winc/Java/postgresql-jdbc-8.0-312.src/org/postgresql/jdbc2/AbstractJdbc2Statement.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);
+++ 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
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:
> 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/
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
-------------------------------
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
В списке pgsql-jdbc по дате отправления: