#126 Datetimes prior to 1899-12-30 are off by up to 24 hours (encoding/decoding error)

[Eirik Bakke]

There seems to be a discrepancy between the way MS Access encodes datetimes prior to 1899-12-30 and how Jackcess encodes/decodes them; this discrepancy can cause datetimes to be wrong by up to 24 hours. I think I've figured out why... here goes my theory:

Microsoft Access bases its encoding of datetimes on double values where the integer part represents the date and the fractional part represents the time. The value of 0.0 corresponds to the epoch datetime "1899-12-30T00:00:00". For dates before the epoch, the value is negative. However, the fractional time part is never considered negative--only dates are. For this reason the conversion done in c.h.jackcess.impl.ColumnImpl.fromDateDouble and toDateDouble does not work for negative date doubles.

Essentially, the date and the time portions of the double must be masked out and calculated separately, and then combined together again. Example:

Value      Date Part           Time Part
+1.625     +1 = 1899-12-31     +0.625 = 15:00 (3pm)
-1.625     -1 = 1899-12-29     +0.625 = 15:00 (3pm--note, no negative sign here)

I've attached an Access file that contains some sample datetimes along with a text column describing what I actually typed into Access to get those datetimes. The code at https://gist.github.com/eirikbakke/7b303a8c2a0b7c62d0f6 shows the workaround I'm using for now to get the correct results for all dates (line 103).

The attached example was created in Microsoft Access 2013. I'm using Jackcess 2.1.1 on Java 1.8.0_31 for MacOS.

[Gord Thompson]

I have confirmed your findings. I typed

1899-12-29 06:00:00

into a table in Access itself, and when I retrieved it using Jackcess I got

1899-12-28 18:00:00

(The Double value in question was -1.25.)

[James Ahlborn]

Are you using the same timezone in jackcess that you are using in access?

[Eirik Bakke]

As far as I understand, Access does not query the system timezone when encoding or displaying datetimes; I've opened the attached example in Access 2013 under many different time zones, and the datetimes always show the same values. Having a time zone setting in Jackcess is actually a bit odd for this reason--Access dates really should convert to a java.time.LocalDateTime (which conceptually represents the exact string "YYYY-MM-DD HH:MM:SS") rather than a java.util.Date (which conceptually represents a point in time, which can not be derived from an MS Access datetime due to the lack of a stored time zone offset). Let me know if I'm all wrong about this... I'd like to get this right in my own software.

But the answer is yes--when both Jackcess and the computer on which Access is running is set to UTC, the discrepancy is the same.

[James Ahlborn]

everything i understand about access databases is that datetimes are in local time. there is no timezone info in an access database that i am aware of. all usage of jackcess by other users to date has confirmed this. if it were not the case, people using jackcess in other countries would have issues.

actually, re-reading your comment, i think you are essentially agreeing with this. LocalDateTime is a new java construct and therefore not currently usable by jackcess (hence the need for a timezone to accurately convert to a Date).

[Eirik Bakke]

actually, re-reading your comment, i think you are essentially agreeing with this

Correct, my understanding is exactly what you wrote.

LocalDateTime is a new java construct and therefore not currently usable by jackcess (hence the need for a timezone to accurately convert to a Date).

Makes sense. I've worked around this in my own client code by setting Jackcess to use UTC and then doing LocalDateTime.ofInstant(date.toInstant(), ZoneId.of("UTC")) on the Date object returned by Jackcess.

[James Ahlborn]

The access epoch you mention is "1899-12-30T00:00:00", but jackcess is currently using "1900-01-01T00:00:00". that's a 2 day differential?

[Eirik Bakke]

I think the comment on the ColumnImpl.MILLIS_BETWEEN_EPOCH_AND_1900 constant is wrong; printing Instant.ofEpochMilli(-MILLIS_BETWEEN_EPOCH_AND_1900).atZone(UTC_ZONEID) yields "1899-12-30T00:00Z[UTC]", not 1900-01-01. The current constant value seems right--I don't think this is the problem.

(Edit: Then the name of the constant is also a little bit misleading...)

[James Ahlborn]

i think you are correct, although i'm confused about how it ended up with that mismatch.

[James Ahlborn]

I believe this is the problem:

https://support.microsoft.com/en-us/kb/214058

[Eirik Bakke]

I think not--the incorrect datetimes are not a constant number of days off. Rather, if you stripped off the fractional part of the datetime double, then you'd get the correct dates (without times). To get the correct time portion, the absolute value of the fractional part should be used. Let me try to code up an example...

[James Ahlborn]

okay, i think i see where you are going with this now. i'm still confused as to why the epoch start date is the 30th, but i guess that's already correct.

[Eirik Bakke]

Maybe obscure historical reasons. (For an entertaining read, see http://www.joelonsoftware.com/items/2006/06/16.html .)

[James Ahlborn]

Yes, that's essentially the issue i posted at the top of this stretch of comments. i think it's all related to that.

[Eirik Bakke]

Here's a gist that shows how ColumnImpl.fromDateDouble might be modified to handle negative dates correctly (something similar would have to be done in toDateDouble):

https://gist.github.com/eirikbakke/af72fed74ed7a4d6272b

The adjustNegativeDatesFromAccess method shows the general logic. adjustNegativeDatesFromAccess2 shows how the logic can be simplified in the negative case. I tested this on the example file I attached, and the dates retrieved now match what Access shows me both before and after the epoch.

[James Ahlborn]

yeah, that's sort of what i was thinking, however i think i will do the math as longs instead of doubles to avoid adding error (the date handling is already a bit touchy).

[Eirik Bakke]

Ah, tricky. Careful with the call to Math.round() in that case; rounding of the time part should never change the date part (?). Maybe something like this is safest (tested on the same dataset):

  long datePart = ((long) value) * (long) MILLISECONDS_PER_DAY;
  long timePart = Math.round(Math.abs(value % 1.0) * MILLISECONDS_PER_DAY);
  long time = datePart + timePart;

Edit: My brain hurts now. Not sure whether my comment about Math.round() is accurate or not.

[Eirik Bakke]

Here are the conversions in both directions: https://gist.github.com/eirikbakke/f006e0c5c15ffc4f86b0

I tested to ensure they were reversible for every millisecond Date between (-MILLIS_BETWEEN_EPOCH_AND_1900 - 100000000) and (-MILLIS_BETWEEN_EPOCH_AND_1900 + 100000000).

[James Ahlborn]

fixed in trunk, will be in the 2.1.2 release.