How to store a Java Instant in a MySQL database

Question

With Java Date objects the easiest way to go was to store them as MySql DateTime objects (in UTC). With the switch to Instant this approach won't work anymore because MySQL DateTime does not offer the precision to store nanoseconds. Just truncating them could lead to unexpected comparison results between a newly created Instant objects and the ones read from the database.

BigDecimal timestamps don't strike me as an elegant solution: writing select queries manually becomes more difficult because you have to convert the timestamp everywhere to make it readable, and the handling in Java is somewhat clunky compared to Instant or even Long values.

What's the best way to go here? Probably not varchar, right?

Answer 1

Truncate to microseconds

Obviously we cannot squeeze the nanoseconds resolution of an Instant into the microseconds resolution of the MySQL data types DateTime and Timestamp.

While I do not use MySQL, I imagine the JDBC driver is built to ignore the nanoseconds when receiving an Instant, truncating the value to microseconds. I suggest you try an experiment to see, and perhaps examine source code of your driver that complies with JDBC 4.2 and later.

Instant instant = Instant.now().with( ChronoField.NANO_OF_SECOND , 123_456_789L ) ;  //Set the fractional second to a spefic number of nanoseconds.
myPreparedStatement.setObject( … , instant ) ;

…and…

Instant instant2 = myResultSet.getObject( … , Instant.class ) ;

The JDBC 4.2 spec requires support for OffsetDateTime but oddly does not require the two more commonly used types, Instant and ZonedDateTime. If your JDBC driver does not support Instant, convert.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;  // Use `OffsetDateTime` if your JDBC driver does not support `Instant`. 
Instant instant2 = odt.toInstant() ;  // Convert from `OffsetDateTime` to `Instant`. 

Then compare.

Boolean result = instant.equals( instant2 ) ;
System.out.println( "instant: " + instant + " equals instant2: = " + instant2 + " is: " + result ) ;

You wisely are concerned about values drawn from the database not matching the original value. One solution, if acceptable to your business problem, is to truncate any nanoseconds to microseconds in your original data. I recommend this approach generally.

The java.time classes offer a truncatedTo method. Pass a ChronoUnit enum object to specify the granularity. In this case, that would be ChronoUnit.MICROS.

Instant instant = Instant().now().truncatedTo( ChronoUnit.MICROS ) ;

Currently this approach should suffice as you are unlikely to have any nanoseconds in your data. Mainstream computers today do not sport hardware clocks capable of capturing nanoseconds, as far as I know.

Count from epoch

If you cannot afford to lose any nanosecond data that may be present, use a count-from-epoch.

I usually recommend against tracking date-time as a count from an epoch reference date. But you have few other choices in storing your nanosecond-based values in a database such as MySQL and Postgres limited to microsecond-based values.

Store pair of integers

Rather than using the extremely large number of nanoseconds since an epoch such as 1970-01-01T00:00Z, I suggest following the approach taken by the internals of the Instant class: Use a pair of numbers.

Store a number of whole seconds as an integer in your database. In a second column store as an integer the number of nanoseconds in the fractional second.

You can easily extract/inject these numbers from/to an Instant object. Only simple 64-bit long numbers are involved; no need for BigDecimal or BigInteger. I suppose you might be able to use a 32-bit integer column for at least one of the two numbers. But I would choose 64-bit integer column types for simplicity and for direct compatibility with the java.time.Instant class’ pair of longs.

long seconds = instant.getEpochSecond() ;
long nanos = instant.getNano() ;

…and…

Instant instant = Instant.ofEpochSecond( seconds , nanos ) ;

When sorting chronologically, you'll need to do a multi-level sort, sorting first on the whole seconds column and then sorting secondarily on the nanos fraction-of-second column.