#599 JtdsPreparedStatement setTimestamp DST Bug

[grimlock81]

Related to
JtdsPreparedStatement setTime setTimestamp Daylight DST Bug - ID: 1176221
https://sourceforge.net/tracker/index.php?func=detail&aid=1176221&group_id=33291&atid=407762

Some background:

1. Our application stores all our dates in GMT. Hence when we insert dates using a PreparedStatement we use the method public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) method passing in a Calendar.getInstance(TimeZone.getTimeZone("GMT"))
2. We are based in Sydney, Australia and hence our test instances have a default TimeZone of Australian Eastern Standard Time (AEST)
3. Daylight saving started in Sydney on the 4th October 2009 0200 (AEST) or equivalently in GMT 3rd October 2009 1600 (GMT)

The problem:

We had tests set up to test whether the date/times inserted into a SQL Server 2005 over this daylight saving changeover period were correct. We found they were correct except for a 1 hour block between 4th October 2009 1300-1400 (AEST) or 4th October 0200-0300 (GMT). These were always stored one hour ahead.

I had a look at the source code, I think the problem lies in the method Support.timeFromZone(java.util.Date value, Calendar target)After reading through this thread, I gather the purpose of this method was to convert the Date object value to a UTC long. This long will have the equivalent 'wall' time in the local time zone as value has in the time zone stored in the Calendar object target.

Problems arise when the 'wall' time contains values that cannot be represented in the local timezone. In our example, since daylight saving in Sydney begins at 4th October 2009 0200 (clocks shift forward one hour to 0300), the period from 0200-0300 does not a valid date/time for the timezone AEST. Hence when Support.timeFromZone encounters dates that have a wall time that lie in this period (i.e. 4th October 0200-0300 (GMT)) , the static Calendar object Support.cal shifts the hour-of-day field 1 hour forward resulting in all dates in this period being stored in the database 1 hour forward.

Attached is a JUnit test case that fails on v 1.2.4

See discussion at
https://sourceforge.net/projects/jtds/forums/forum/129584/topic/1260179/index/page/1

[grimlock81]

JUnit test case

[grimlock81]

Proposed solution:
Have net.sourceforge.jtds.jdbc.Support, always use a Calendar that has a TimeZone that can support all possible date/times. The simplest one would be the GMT timezone.

This will require code handling the new Timestamp object further down the track e.g. net.sourceforge.jtds.jdbc.DateTime will also have to use a Calendar object with the GMT timezone. We will have to watch out for other classes that use the DateTime class as well (yet to be surveyed)

Currently net.sourceforge.jtds.jdbc.JtdsPreparedStatement has the method
public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal)
call
public void setTimestamp(int parameterIndex, Timestamp x)
after calling Support.timeFromZone() to convert the Timestamp. This logic would have to be reversed to that the latter calls the former while passing in a GMT Calendar.

I'll submit a patch after I run the unit tests.

[grimlock81]

I just came across patch ID 2731952

https://sourceforge.net/tracker/?func=detail&aid=2731952&group_id=33291&atid=407764

which the submitter claims to have overcome this problem. However, it seems the patch hasn't been accepted, and it was also targeted for version 1.2.2, which of course is now obsolete.

I'll have to download 1.2.2 and apply the changes to that version and give my opinion on it.

[Shailender Bathula]

Submitted patch #124 as a fix for this bug.