How to save Java Instant in MySQL database

Question

With Java objects, Datethe easiest way is to store them as MySql objects DateTime(in UTC format). The transition to Instantthis approach will no longer work, because MySQL DateTimedoes not offer precision for storing nanoseconds. Just truncating them can lead to unexpected comparison results between newly created objects Instantand those that are read from the database.

BigDecimaltimestamps don't hit me as an elegant solution: writing a select query in MySql Workbench is getting harder because you need to convert the timestamp everywhere to make it readable, and processing in Java is somewhat inconvenient to compare before Instantor even Long.

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

Answer 1

Truncate to microseconds

Obviously, we cannot compress the resolution of nanoseconds Instantto the resolution of microseconds for MySQL data types DateTimeand Timestamp.

I believe that the JDBC driver is built to ignore nanoseconds at reception Instant, reducing the value to microseconds. I suggest you try the experiment and maybe study the source code of your driver, which corresponds to 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 ) ;

Then compare.

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

, , . , -, . .

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

, - . , , .

- , , count-from-epoch.

-, . , MySQL Postgres, .

, 1970-01-01T00: 00Z, , Instant: .

. .

/ / Instant. 64- long; BigDecimal BigInteger. , 32- . 64- java.time.Instant.

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

... ...

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

, , " ".