How to store a Java Instant in a MySQL database
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.
Related videos on Youtube
NotX
Updated on July 09, 2022Comments
-
NotX almost 2 years
With Java
Date
objects the easiest way to go was to store them as MySqlDateTime
objects (in UTC). With the switch toInstant
this approach won't work anymore because MySQLDateTime
does not offer the precision to store nanoseconds. Just truncating them could lead to unexpected comparison results between a newly createdInstant
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 toInstant
or evenLong
values.What's the best way to go here? Probably not
varchar
, right?-
Tim Biegeleisen over 6 years@OleV.V. OK but the MySQL docs seem to imply that timestamp can only handle up to microseconds. What happens to the nanoseconds component?
-
Ole V.V. over 6 yearsI cannot come up with better suggestions than your own. If you convert your
Instant
toOffsetDateTime
at offsetZoneOffset.UTC
and format it usinguuuu-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 anInstant
(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. -
NotXOf 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. -
Ole V.V.Interesting question, @TimBiegeleisen. The docs seem to confirm. Seems we’ve got to be creative about it.
-
NotX@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 anInstant
created at runtime is not equal to this veryInstant
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.
-
-
Basil Bourque over 3 years@MathewAlden Thanks for your fix. That prompted me to add a few other tweaks to this Answer.