#304 New Handling of Timestamp in 2.4.8 has error with subseconds

[Chris Pheby]

When updating the Usertype project (http://usertype.sourceforge.net/) to dbunit 2.4.8, I have found a defect in handling of timestamps.

The change in revision 1131 (bug 2921131) introduces a defect because the handling of zero padding in the millisecond field
differs between SimpleDateFormat and the JDBC escape format for Timestamp.

in SimpleDateFormat .5 gives 005 millis
in JDBC escape format .5 gives 500 millis

In addition, the change in this version removes support for nanosecond precision from the timestamp.

I have included a revised patch which I believe addresses the above problems (including additions to the tests that demonstrate it)
whilst supporting the improvement the original patch aimed to provide (i.e. zone support).

Regards Chris

[Chris Pheby]

Fixes to timestamp conversion and unit test case

[Roberto Lo Giacco]

Your fix has been accepted and committed to the project repository.

Thank you!

[fs5]

This bugfix causes regressions in our test-suites.

IMHO, the provided fix is flawed as it does not take into account that java.sql.Timestamp.parse(String) is always local-time.

In consequence, the following comparisons fail for me (Europe/Berlin, GMT+1, JDK 6)

private final static DataType THIS_TYPE = DataType.TIMESTAMP;

public void testWithTimezone_LocalTZ() throws Exception
{
    Timestamp ts1 = makeTimestamp(2013, 0, 27, 1, 22, 41, 900, "GMT+1");
    String ts2 = "2013-01-27 01:22:41.900 +0100";
    assertEquals(ts1, THIS_TYPE.typeCast(ts2));
}

public void testWithTimezone_GMT6() throws Exception
{
    Timestamp ts1 = makeTimestamp(2013, 0, 27, 1, 22, 41, 900, "GMT+6");
    String ts2 = "2013-01-27 01:22:41.900 +0600";
    assertEquals(ts1, THIS_TYPE.typeCast(ts2));
}

My proposed fix of the previous fix (without saying that this is the way it should be implemented):

Index: src/main/java/org/dbunit/dataset/datatype/TimestampDataType.java
===================================================================
--- src/main/java/org/dbunit/dataset/datatype/TimestampDataType.java    (revision 1263)
+++ src/main/java/org/dbunit/dataset/datatype/TimestampDataType.java    (working copy)
@@ -27,6 +27,7 @@
 import java.sql.SQLException;
 import java.sql.Timestamp;
 import java.sql.Types;
+import java.util.TimeZone;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;

@@ -124,7 +125,9 @@
          // Apply zone if any
          if (zoneValue != null)
          {
-             BigInteger time = BigInteger.valueOf(ts.getTime() / 1000 * 1000).multiply(ONE_BILLION).add(BigInteger.valueOf(ts.getNanos()));
+             TimeZone localTZ = java.util.TimeZone.getDefault();
+             BigInteger localTZOffset = BigInteger.valueOf(localTZ.getRawOffset());
+             BigInteger time = BigInteger.valueOf(ts.getTime() / 1000 * 1000).add(localTZOffset).multiply(ONE_BILLION).add(BigInteger.valueOf(ts.getNanos()));
              int hours = Integer.parseInt(zoneValue.substring(1, 3));
              int minutes = Integer.parseInt(zoneValue.substring(3, 5));
              BigInteger offsetAsSeconds = BigInteger.valueOf((hours * 3600) + (minutes * 60));

Let's say you are GMT+1 and you are parsing (just the time-component for brevity): "12:00 +0600". With java.sql.Timestamp.valueOf("12:00") you will get "12:00 +0100". To transpose to local-time you have to calculate "12:00 +0100" -0600 +0100 = "07:00 +0100" (and "06:00 +0000")