jTDS - SQL Server and Sybase JDBC driver

Tracker: Bugs

5 JtdsPreparedStatement setTimestamp DST Bug - ID: 2887672

Last Update: Settings changed ( grimlock81 )

Details:

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/ind
ex/page/1

Submitted:

grimlock81 ( grimlock81 ) - 2009-10-28 03:18

Priority:

Status:

Open

Resolution:

None

Assigned:

momo

Category:

Date/time handling

Group:

None

Visibility:

Public

Comments ( 2 )

Date: 2009-11-06 06:53 Sender: 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.
Date: 2009-11-05 14:13 Sender: 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.

Date: 2009-11-06 06:53
Sender: 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.

Date: 2009-11-05 14:13
Sender: 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.

Attached File ( 1 )

Filename	Description	Download
TimeZone2Test.java	JUnit test case	Download

Changes ( 2 )

Field	Old Value	Date	By
assigned_to	alin_sinpalean	2009-11-06 06:53	grimlock81
File Added	348480: TimeZone2Test.java	2009-10-28 03:18	grimlock81

jTDS - SQL Server and Sybase JDBC driver

Tracker: Bugs

Comments ( 2 )

Attached File ( 1 )

Changes ( 2 )

About SourceForge

Find Software

Develop Software

Community

Help