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?

Kizer

I don't think JDBC will be able to directly map a Java 8 Instant to a MySQL column. Internally, an Instant actually uses a long component to store seconds since the epoch, and an integer component to store nanoseconds since the last whole second. I think you would need two columns to cover an Instant in MySQL without losing any information. – Kilroy

@TimBiegeleisen With JDBC 4.2 PreparedStatement.setObject accepts a java.time.Instant and happily stores it to a timestamp column in most databases, I would expect in MySQL too. – Fuddyduddy

@OleV.V. OK but the MySQL docs seem to imply that timestamp can only handle up to microseconds. What happens to the nanoseconds component? – Kilroy

Interesting question, @TimBiegeleisen. The docs seem to confirm. Seems we’ve got to be creative about it. – Fuddyduddy

Of course I could just store the two Instant components in two columns, which would make things even more complicated. I'm just wondering whether there is a common and convenient solution for this not-so-exotic problem. – Kizer

I cannot come up with better suggestions than your own. If you convert your Instant to OffsetDateTime at offset ZoneOffset.UTC and format it using uuuu-MM-dd'T'HH:mm.ss.SSSSSSSSSX, then (1) until year 9999 your string will always be 30 chars (2) the string can be parsed back directly into an Instant (3) the ordering of the strings will be the same as the ordering of the instants, and (4) the strings in your database will be more readable than pure numbers. – Fuddyduddy

Are you certain you really have data in nanoseconds? I do not know of any common computer hardware clocks that capture moments in nanoseconds, only microseconds or milliseconds. – Phosphorus

@Ole.V.V Okay, thanks for all help then! I was just unsure whether I'ld miss something obvious. – Kizer

@Basil Bourque You're right, I don't really need them. In fact, even the documentation for Instant discourages to rely on them. I just want to avoid scenarios where an Instant created at runtime is not equal to this very Instant stored and reloaded from the database, just because the nanos got lost. This is an undocumented side-effect which might screw up UnitTests or worse. – Kizer

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.

Phosphorus

@MathewAlden Thanks for your fix. That prompted me to add a few other tweaks to this Answer. – Phosphorus