From be706546befffe6f746ec9e1f790b2c2a7849700 Mon Sep 17 00:00:00 2001
From: James Ahlborn <jtahlborn@yahoo.com>
Date: Thu, 18 Jun 2015 21:38:58 +0000
Subject: [PATCH] Fix handling of dateTimes before the access epoch. fixes
 issue #126

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@950 f203690c-595d-4dc9-a70b-905162fa7fd2
---
 src/changes/changes.xml                       |  3 ++
 .../jackcess/impl/ColumnImpl.java             | 47 +++++++++++++++----
 .../jackcess/impl/DatabaseReadWriteTest.java  | 27 +++++++++++
 3 files changed, 69 insertions(+), 8 deletions(-)

diff --git a/src/changes/changes.xml b/src/changes/changes.xml
index 00cfe01..b881a6e 100644
--- a/src/changes/changes.xml
+++ b/src/changes/changes.xml
@@ -8,6 +8,9 @@
       <action dev="jahlborn" type="fix" system="SourceForge2" issue="125">
         Handle reading null calculated values.
       </action>
+      <action dev="jahlborn" type="fix" system="SourceForge2" issue="126">
+        Fix handling of dateTimes before the access epoch.
+      </action>
     </release>
     <release version="2.1.1" date="2015-05-14">
       <action dev="jahlborn" type="fix" system="SourceForge2" issue="123">
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/ColumnImpl.java b/src/main/java/com/healthmarketscience/jackcess/impl/ColumnImpl.java
index 6465512..00ce9c7 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/ColumnImpl.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/ColumnImpl.java
@@ -76,15 +76,17 @@ public class ColumnImpl implements Column, Comparable<ColumnImpl> {
   /**
    * Access stores numeric dates in days.  Java stores them in milliseconds.
    */
-  private static final double MILLISECONDS_PER_DAY =
+  private static final long MILLISECONDS_PER_DAY =
     (24L * 60L * 60L * 1000L);
 
   /**
-   * Access starts counting dates at Jan 1, 1900.  Java starts counting
-   * at Jan 1, 1970.  This is the # of millis between them for conversion.
+   * Access starts counting dates at Dec 30, 1899 (note, this strange date
+   * seems to be caused by MS compatibility with Lotus-1-2-3 and incorrect
+   * leap years).  Java starts counting at Jan 1, 1970.  This is the # of
+   * millis between them for conversion.
    */
-  private static final long MILLIS_BETWEEN_EPOCH_AND_1900 =
-    25569L * (long)MILLISECONDS_PER_DAY;
+  static final long MILLIS_BETWEEN_EPOCH_AND_1900 =
+    25569L * MILLISECONDS_PER_DAY;
   
   /**
    * mask for the fixed len bit
@@ -789,9 +791,24 @@ public class ColumnImpl implements Column, Comparable<ColumnImpl> {
    */
   public long fromDateDouble(double value)
   {
-    long time = Math.round(value * MILLISECONDS_PER_DAY);
+    long localTime = fromLocalDateDouble(value);
+    return localTime - getFromLocalTimeZoneOffset(localTime);
+  }
+
+  static long fromLocalDateDouble(double value)
+  {
+    long datePart = ((long)value) * MILLISECONDS_PER_DAY;
+
+    // the fractional part of the double represents the time.  it is always
+    // a positive fraction of the day (even if the double is negative),
+    // _not_ the time distance from zero (as one would expect with "normal"
+    // numbers).  therefore, we need to do a little number logic to convert
+    // the absolute time fraction into a normal distance from zero number.
+    long timePart = Math.round((Math.abs(value) % 1.0) * 
+                               (double)MILLISECONDS_PER_DAY);
+
+    long time = datePart + timePart;
     time -= MILLIS_BETWEEN_EPOCH_AND_1900;
-    time -= getFromLocalTimeZoneOffset(time);
     return time;
   }
 
@@ -825,8 +842,22 @@ public class ColumnImpl implements Column, Comparable<ColumnImpl> {
     // hope you read it in the same timezone in which it was written!
     long time = toDateLong(value);
     time += getToLocalTimeZoneOffset(time);
+    return toLocalDateDouble(time);
+  }
+
+  static double toLocalDateDouble(long time)
+  {
     time += MILLIS_BETWEEN_EPOCH_AND_1900;
-    return time / MILLISECONDS_PER_DAY;
+
+    if(time < 0L) {
+      // reverse the crazy math described in fromLocalDateDouble
+      long timePart = -time % MILLISECONDS_PER_DAY;
+      if(timePart > 0) {
+        time -= (2 * (MILLISECONDS_PER_DAY - timePart));
+      }
+    }
+
+    return time / (double)MILLISECONDS_PER_DAY;
   }
 
   /**
diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/DatabaseReadWriteTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/DatabaseReadWriteTest.java
index fcc9176..561f1e8 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/DatabaseReadWriteTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/DatabaseReadWriteTest.java
@@ -230,4 +230,31 @@ public class DatabaseReadWriteTest extends TestCase
       db.close();
     }
   }
+
+  public void testDateMath()
+  {
+    long now = System.currentTimeMillis();
+
+    // test around current time
+    doTestDateMath(now);
+
+    // test around the unix epoch
+    doTestDateMath(0L);
+
+    // test around the access epoch
+    doTestDateMath(-ColumnImpl.MILLIS_BETWEEN_EPOCH_AND_1900);
+  }
+
+  private static void doTestDateMath(long testTime)
+  {
+    final long timeRange = 100000000L;
+    final long timeStep = 37L;
+
+    for(long time = testTime - timeRange; time < testTime + timeRange; 
+        time += timeStep) {
+      double accTime = ColumnImpl.toLocalDateDouble(time);
+      long newTime = ColumnImpl.fromLocalDateDouble(accTime);
+      assertEquals(time, newTime);
+    }
+  }
 }
-- 
2.39.5