Mark Meyers Mark Meyers - 5 months ago 142
Java Question

MySQL DATETIME and TIMESTAMP to java.sql.Timestamp to ZonedDateTime

I am wondering how the conversion works in this. MySQL server (5.6) treats TIMESTAMP as zone-adjusted (and internally stored in/retrieved from UTC). It also treats DATETIME as having no zone.

On the Java side, I am recommended to read into

java.sql.Timestamp
in either case. Is there a zone-type conversion taking place (when going through MySQL-connector 5.1.37) from MySQL's DATETIME to
java.sql.Timestamp
(such as to apply the client system zone) ?

In the end, there is only one zone for my server and clients, and so I maintain a specific ZoneId (in app code) to get to
ZonedDateTime
. But I would like to work with
ZonedDateTime
, going back and forth to the database stored as DATETIME. A simple example of conversion will be appreciated!

Answer

Let's address each question you have. First:

Is there a zone-type conversion taking place (when going through MySQL-connector 5.1.37) from MySQL's DATETIME to java.sql.Timestamp (such as to apply the client system zone)?
First off, I presume that you are using the getTimestamp(int) method from the connector. I could not find an official source that showed me an enlightening answer; however, there was this question in which the answer stated:
When you call getTimestamp(), MySQL JDBC driver converts the time from GMT into default timezone if the type is timestamp. It performs no such conversion for other types.
However, in this version of the method, it uses an underlying Calendar to convert the Timestamp to the TimeZone specified, if the underlying database doesn't store time zone information. This may be the solution to your second question, as long as you knew the time zone at which the value was stored (which you do). But if it is not, it seems that with the first method there is no conversion taking place, at least when it retrieves the DATETIME. Speaking about your second question:
But I would like to work with ZonedDateTime, going back and forth to the database stored as DATETIME.
It makes me think that there is a way to do this as long as you knew which time zone you are converting from. As we have previously stated, you and your clients are only working with one ZoneId, which is totally fine. However, this answer is provided to work with more time zones. Multiple ZoneId's can be achieved if you were to store the ZoneId of the connection in the database; retrieving it as well as the DATETIME and finally processing these values into a ZonedDateTime. You could store the ZoneIds into the database using the ID's of the ZoneId class (if you wanted to).

Timestamp t = resultSet.getTimestamp(timestampColumnId);
ZoneId zoneId = ZoneId.of(resultSet.getString(zoneColumnId), ZoneId.SHORT_IDS);
ZonedDateTime d = ZonedDateTime.ofInstant(t.toInstant(), zoneId);

Or, you could just store the DATETIME as a TIMESTAMP in the database as ZZ Coder suggests in his answer stated above. But, you could just use the ZoneId you have hard-coded as such:

Timestamp t = resultSet.getTimestamp(timestampColumnId);
ZonedDateTime d = ZonedDateTime.ofInstant(t.toInstant(), zoneId);