JDBC MySQL reading and writing TIMESTAMP as milliseconds since Unix Epoch
I have a TIMESTAMP column in a MySQL table which I am accessing through JDBC. On the Java side, I am using JodaTime.
I would like to represent all my instants as milliseconds since the Unix epoch. I would just use an integer field, but I want to use the ON UPDATE CURRENT_TIMESTAMP syntax which is only supported for TIMESTAMP/DATETIME types.
JodaTime allow me to easily convert between the different representations and milliseconds since epoch, but it's not so simple to use milliseconds since epoch with JDBC/MYSQL.
Is there any way I can us JDBC to store milliseconds_since_epoch in a TIMESTAMP column, and retrieve TIMESTAMP columns as milliseconds_since_epoch without having to worry about the values changing due to the client or the server changing timezones.
I would prefer to not have to mess with mysql server settings or jdbc connection settings, but I would be willing to if it's the only way.
Is there any way I can us JDBC to store milliseconds_since_epoch in a TIMESTAMP column, and retrieve TIMESTAMP columns as milliseconds_since_epoch
For MySQL Server versions prior to 5.6.4:
No. A TIMESTAMP column will discard fractional seconds (ref: here).
If you really need to store milliseconds then you'll have to put them in a separate numeric column. However, if you can make do with time resolution of whole seconds then a TIMESTAMP column will automatically convert values to UTC when they are stored (ref: here).
For MySQL Server versions 5.6.4 and later:
Yes. See the following MySQL documentation topic for more information:
Fractional Seconds in Time Values
Let your tools worry about the milliseconds
You are fighting against the type system of SQL, JDBC, and Java. Do not worry about the milliseconds. Your database, driver, and Java are already doing that for you, but finer (microseconds in MySQL, nanoseconds in Java).
I would like to represent all my instants as milliseconds since the Unix epoch.
This is exactly what MySQL is doing for you in its TIMESTAMP data type, only finer - A count of microseconds since start of 1970 in UTC. To quote the doc:
TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
…
TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision
Your concern about staying in UTC…
retrieve TIMESTAMP columns as milliseconds_since_epoch without having to worry about the values changing due to the client or the server changing timezones
…is already addressed. MySQL is storing a TIMESTAMP in UTC, your JDBC driver should be retrieving the value in UTC, and the java.time class Instant stores the value in UTC. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction). So you have UTC only, all the way through.
Avoid the troublesome old date-time classes whenever possible. Instead use their replacement, the java.time classes. If your JDBC driver is updated for JDBC 4.2 and later you can work directly with java.time types.
Instant instant = Instant.now() ; // Current moment in UTC with resolution up to nanoseconds.
myPreparedStatement.setObject( … , instant ) ;
…and…
Instant instant = myResultSet.getObject( … , Instant.class ) ;
If your JDBC driver is not yet compliant, briefly use the old java.sql types but convert immediately to/from java.time. Do not perform business logic with the java.sql types.
myPreparedStatement.setTimestamp( … , java.sql.Timestamp.from( instant ) ) ;
…and…
Instant instant = myResultSet.getTimestamp( … ).toInstant() ;
When you want to adjust out of UTC and into a time zone such as for presentation to a user, apply a ZoneId to get a ZonedDateTime . Search Stack Overflow for many more examples.
ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ; // Same moment, different wall-clock time.
If you insist on accessing milliseconds-since-epoch, interrogate the Instant object. But beware of data-loss, as that instant may hold microseconds from MySQL or nanoseconds from other sources. Asking for milliseconds means truncating the finer fraction of a second.
long millisSinceEpoch = instant.toEpochMilli() ;
Going the other direction.
Instant instant = Instant.ofEpochMilli( millisSinceEpoch ) ;
About java.time
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date , Calendar , & SimpleDateFormat .
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval , YearWeek , YearQuarter , and more.