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
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 toFirst 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:
java.sql.Timestamp(such as to apply the client system zone)?
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
Calendarto convert the
TimeZonespecified, 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 withIt 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
ZonedDateTime, going back and forth to the database stored as DATETIME.
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
ZoneIdof the connection in the database; retrieving it as well as the
DATETIMEand finally processing these values into a
ZonedDateTime. You could store the
ZoneIds into the database using the ID's of the
ZoneIdclass (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);