Fix some more edge cases in date/time conversions (fixes issue 92)

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@645 f203690c-595d-4dc9-a70b-905162fa7fd2

3 files changed, 64 insertions, 7 deletions

diff --git a/src/changes/changes.xml b/src/changes/changes.xml
index acccf8b..604084c 100644
--- a/src/changes/changes.xml
+++ b/src/changes/changes.xml
@@ -17,6 +17,9 @@
         Added the MemFileChannel (and associated support in DatabaseBuilder)
         to enable working with Database files completely in memory.
       </action>
+      <action dev="jahlborn" type="fix" issue="92">
+        Fix some more edge cases in date/time conversions.
+      </action>
     </release>
     <release version="1.2.8" date="2012-07-10">
       <action dev="jahlborn" type="update" issue="3523179">
@@ -184,7 +187,7 @@
         ColumnBuilder to optionally escape names.
       </action>
       <action dev="jahlborn" type="update" issue="2997751">
-        Add support for overriding charset and tiemzone used when
+        Add support for overriding charset and timezone used when
         reading/writing database.
       </action>
       <action dev="jahlborn" type="add" issue="3003375">
diff --git a/src/java/com/healthmarketscience/jackcess/Column.java b/src/java/com/healthmarketscience/jackcess/Column.java
index cd28621..e5503bd 100644
--- a/src/java/com/healthmarketscience/jackcess/Column.java
+++ b/src/java/com/healthmarketscience/jackcess/Column.java
@@ -1093,11 +1093,11 @@ public class Column implements Comparable<Column> {
   /**
    * Returns a java long time value converted from an access date double.
    */
-  private long fromDateDouble(double value)
+  long fromDateDouble(double value)
   {
     long time = Math.round(value * MILLISECONDS_PER_DAY);
     time -= MILLIS_BETWEEN_EPOCH_AND_1900;
-    time -= getTimeZoneOffset(time);
+    time -= getFromLocalTimeZoneOffset(time);
     return time;
   }
 
@@ -1124,7 +1124,7 @@ public class Column implements Comparable<Column> {
    * Returns an access date double converted from a java Date/Calendar/Number
    * time value.
    */
-  private double toDateDouble(Object value)
+  double toDateDouble(Object value)
   {
       // seems access stores dates in the local timezone.  guess you just
       // hope you read it in the same timezone in which it was written!
@@ -1133,15 +1133,16 @@ public class Column implements Comparable<Column> {
                    ((value instanceof Calendar) ?
                     ((Calendar)value).getTimeInMillis() :
                     ((Number)value).longValue()));
-      time += getTimeZoneOffset(time);
+      time += getToLocalTimeZoneOffset(time);
       time += MILLIS_BETWEEN_EPOCH_AND_1900;
       return time / MILLISECONDS_PER_DAY;
   }
 
   /**
-   * Gets the timezone offset from UTC for the given time (including DST).
+   * Gets the timezone offset from UTC to local time for the given time
+   * (including DST).
    */
-  private long getTimeZoneOffset(long time)
+  private long getToLocalTimeZoneOffset(long time)
   {
     Calendar c = Calendar.getInstance(getTimeZone());
     c.setTimeInMillis(time);
@@ -1149,6 +1150,21 @@ public class Column implements Comparable<Column> {
   }  
   
   /**
+   * Gets the timezone offset from local time to UTC for the given time
+   * (including DST).
+   */
+  private long getFromLocalTimeZoneOffset(long time)
+  {
+    // getting from local time back to UTC is a little wonky (and not
+    // guaranteed to get you back to where you started)
+    Calendar c = Calendar.getInstance(getTimeZone());
+    c.setTimeInMillis(time);
+    // apply the zone offset first to get us closer to the original time
+    c.setTimeInMillis(time - c.get(Calendar.ZONE_OFFSET));
+    return ((long)c.get(Calendar.ZONE_OFFSET) + c.get(Calendar.DST_OFFSET));
+  }  
+  
+  /**
    * Decodes a GUID value.
    */
   private static String readGUIDValue(ByteBuffer buffer, ByteOrder order)
diff --git a/test/src/java/com/healthmarketscience/jackcess/DatabaseTest.java b/test/src/java/com/healthmarketscience/jackcess/DatabaseTest.java
index 1c0c2eb..5cb680c 100644
--- a/test/src/java/com/healthmarketscience/jackcess/DatabaseTest.java
+++ b/test/src/java/com/healthmarketscience/jackcess/DatabaseTest.java
@@ -1312,6 +1312,44 @@ public class DatabaseTest extends TestCase {
     }
   }
 
+  public void testTimeZone() throws Exception
+  {
+    TimeZone tz = TimeZone.getTimeZone("America/New_York");
+    doTestTimeZone(tz);
+
+    tz = TimeZone.getTimeZone("Australia/Sydney");
+    doTestTimeZone(tz);
+  }
+
+  private static void doTestTimeZone(final TimeZone tz) throws Exception
+  {
+    Column col = new Column(true, null) {
+      @Override
+      protected TimeZone getTimeZone() { return tz; }
+    };
+
+    SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");
+    df.setTimeZone(tz);
+
+    long startDate = df.parse("2012.01.01").getTime();
+    long endDate = df.parse("2013.01.01").getTime();
+
+    Calendar curCal = Calendar.getInstance(tz);
+    curCal.setTimeInMillis(startDate);
+
+    SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
+    sdf.setTimeZone(tz);
+
+    while(curCal.getTimeInMillis() < endDate) {
+      Date curDate = curCal.getTime();
+      Date newDate = new Date(col.fromDateDouble(col.toDateDouble(curDate)));
+      if(curDate.getTime() != newDate.getTime()) {
+        assertEquals(sdf.format(curDate), sdf.format(newDate));
+      }
+      curCal.add(Calendar.MINUTE, 30);
+    }
+  }
+
   private void checkRawValue(String expected, Object val)
   {
     if(expected != null) {