How do I convert from a long to an Access Date/Time value in Jackcess?

Question

I have the following code:

// Model the table
  Table tbl = new TableBuilder("Transactions")
    .addColumn(new ColumnBuilder("TransactionID", DataType.LONG).setAutoNumber(true))
    .addColumn(new ColumnBuilder("ControllerID", DataType.LONG).setAutoNumber(false))
    .addColumn(new ColumnBuilder("ReaderID", DataType.LONG).setAutoNumber(false))
    .addColumn(new ColumnBuilder("Event", DataType.LONG).setAutoNumber(false))
    .addColumn(new ColumnBuilder("Timestamp", DataType.SHORT_DATE_TIME).setAutoNumber(false))
    .addColumn(new ColumnBuilder("Number", DataType.LONG).setAutoNumber(false))
    .addIndex(new IndexBuilder(IndexBuilder.PRIMARY_KEY_NAME).addColumns("TransactionID").setPrimaryKey())
    .toTable(db);
  // Add the row
  Map<String, Object> values = new HashMap<>();
  values.put("ControllerID", cid);
  values.put("ReaderID", rid);
  values.put("Event", evtNum);
  values.put("Timestamp", ts); // Long; must be converted to DataType.SHORT_DATE_TIME
  values.put("Number", accNum);
  tbl.addRowFromMap(values);

I want to convert from a long, ts to a date format that MS Access understands. The type of the "Timestamp" column in MS Access is Date/Time, with values displayed in the form "YYYY-MM-dd HH:mm:ss". What do I need to do to convert ts?

This answer to a similar problem makes mention of conversion to a double (which is what Access uses to store Date/Time fields, according to the source), but I don't understand what I need to do to my code to conform to it:

This also points up the issue of the independence of display format and data storage with Jet/ACE date values. The storage is as a double, with the integer part indicating the day since 12/30/1899 and the decimal part the time portion within the day. Any date you enter is going to be stored as only one number.

— David W. Fenton; 4 July 2010

Note: I know how to convert a long to a java.util.Date, set the time for a java.util.Calendar, then get a human-readable/parsable formatted String from it. That's not what I'm trying to do, which is why marking as a duplicate of the linked question is incorrect. What I'm trying to do is get the double value that MS Access understands.

Answer 1

While it is true that Access stores Date/Time values as Double floating-point numbers, you don't need to do that conversion yourself; Jackcess will do it for you. Just pass a java.util.Date to Jackcess and it will take care of the rest.

If your long ts is the number of milliseconds since the Unix epoch (1970-01-01 00:00:00 UTC) then that could be as simple as

values.put("Timestamp", new java.util.Date(ts));

although Access doesn't really support fractional seconds, so it would be a bit safer to truncate the milliseconds by doing

values.put("Timestamp", new java.util.Date(ts / 1000 * 1000));

Note that such a conversion will use the current time zone of the Java Virtual Machine (JVM).