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

Question

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!

Answered my own question. You must be using Java 8. And my first comment must not have been helpful, as that was all client side interpretation of the server information. My apologies. — astrogeek14, Jun 27 '16 at 22:49
@astrogeek14, I wasn't guessing I would be needing to format/parse strings to do this. ? Right now, I have a DATETIME value coming in from MySQL, and I want to put it into a `ZonedDateTime` using a known `ZoneId` (in a variable we'll call `zid`). — Mark Meyers, Jun 27 '16 at 22:50
You probably won't; I was misinterpreting your question. Continuing: is it possible to put the `ZoneId` into the database (presumably as an varchar of length three)? — astrogeek14, Jun 27 '16 at 22:54
@astrogeek14, I don't know what it would be for. I have a `ZoneId` in my code. I know what zone to convert to (from I presume java.sql.Timestamp). — Mark Meyers, Jun 27 '16 at 23:00

score 2 · Accepted Answer · edited May 23 '17 at 11:58

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);

EDIT
Looking at the source code, on get or set calls using the getTimestamp(int, Calendar) or the setTimestamp(int, Timestamp, Calendar) function, the timezone of the Calendar is used. However, in some cases with TIMESTAMP, when a Calendar is not used, the JDBC then uses the time zone of the server. And according to the original poster, it worked (see comment below).

You know, I just got to this point using test data, writing to the DB and then reading it back, but I wouldn't have known what you came up with about the database. I did this: `Timestamp t = rs.getTimestamp(rsCol); ZonedDateTime zdt = t.toInstant().atZone(zoneId);` — Mark Meyers, Jun 28 '16 at 00:30
In the case of writing to the db, I went with this:`stmt.setTimestamp( i+1, new Timestamp(( (ZonedDateTime) params.get(i)).toInstant().getEpochSecond() * 1000L));` (where i is the index of Vector params being loaded into a prepared statement) — Mark Meyers, Jun 28 '16 at 00:35
The multiplication of `1000L` is necessary because Java uses milliseconds right? While the unix timestamp is in seconds? — astrogeek14, Jun 28 '16 at 00:39
I should say I didn't think to check for various getTimestamp() calls, either. Thank you, @astrogeek14. — Mark Meyers, Jun 28 '16 at 00:40
Try it with, and if you have no problems, then I would assume so. — astrogeek14, Jun 28 '16 at 00:41
I don't know the UNIX Timestamp. Maybe I'm not using the most sound method. — Mark Meyers, Jun 28 '16 at 00:41
No, you are right. See here: http://www.java2s.com/Tutorials/Java/Data_Type_How_to/Date_Convert/Convert_ZonedDateTime_to_java_sql_Timestamp.htm — astrogeek14, Jun 28 '16 at 00:42
Re: your edit-add... In fact, I keep my time zone in the DB, and get it at init, and then use it on client machines. We must be on the same page. — Mark Meyers, Jun 28 '16 at 00:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115771/discussion-between-astrogeek14-and-mark-meyers). — astrogeek14, Jun 28 '16 at 00:48
Note: I added the `Calendar` parameter to both `getTimestamp()` and `setTimestamp()` calls, where all I did to make the Calendar was receive the return from `Calendar.getInstance(myTimeZone)`, and my testing before and after DST seems fine. And in looking at the [source code](https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/ResultSetImpl.java), what MySQL Connector appears to do is use the server's zone unless otherwise specified on the calls. — Mark Meyers, Jun 28 '16 at 02:57

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

1 Answers1