How to convert java.sql.Timestamp to java.time.OffsetDateTime?

Question

I am working on a Scala project and I need to map the OffsetDateTime type to the SQL Timestamp type. In the DB, I would like to have UTC time.

Converting from OffsetDateTime to Timestamp is simple (hint from this question ) and works as expected:

 import java.time._ import java.sql.Timestamp val ofsdatetime = OffsetDateTime.now() // ofsdatetime: java.time.OffsetDateTime = 2017-04-04T21:46:33.567+02:00 val tstamp = Timestamp.valueOf(ofsdatetime.atZoneSameInstant(ZoneOffset.UTC).toLocalDateTime()) // tstamp: java.sql.Timestamp = 2017-04-04 19:46:33.567 

As you can see, the time zone is deleted and the timestamp will return on time (UTC), great!

Converting a Timestamp to OffsetDateTime does not work as expected :

 OffsetDateTime.ofInstant(Instant.ofEpochMilli(tstamp.getTime), ZoneId.systemDefault()) // java.time.OffsetDateTime = 2017-04-04T19:46:33.567+02:00 

The time zone is added to the newly created OffsetDateTime , but the time is incorrect (this is still UTC, I need it to be adapted to the actual time zone).

Why? What am I doing wrong?

Answer 1

Although java.sql.Timestamp stores millions of eras, the .toString method uses the default timezone to render the string. In addition, .valueOf interprets LocalDateTime using the default time zone.

The combination of both things leads to the fact that the first transformation "looks right", but in fact it is incorrect. The value "2017-04-04 19: 46: 33.567" is displayed in your TZ by default, and not in UTC.

Since you passed the valueOf a LocalDateTime (UTC) method, but it interpreted it as LocalDateTime (your default TZ).

Here is the proof that the first conversion is wrong:

 scala> val now = OffsetDateTime.now now: java.time.OffsetDateTime = 2017-04-04T14:50:12.534-06:00 scala> Timestamp.valueOf(now.atZoneSameInstant(ZoneId.of("UTC")).toLocalDateTime).getTime == now.toInstant.toEpochMilli res54: Boolean = false 

Now with the .atZoneSameInstant removed:

 scala> Timestamp.valueOf(now.toLocalDateTime).getTime == now.toInstant.toEpochMilli res53: Boolean = true 

Invalid accepted answer to the specified stacking question.

Once you fix the first conversion (remove .atZoneSameInstant ), your second conversion should work fine.

Answer 2

java.sql.Timestamp is a thin shell around a long value representing milliseconds since the era ( 1970-01-01T00:00:00.000 UTC ), so the UTC time zone is implicit in java.sql.Timestamp. It cannot store time zone information, but it is implicit in UTC, and as long as everyone knows this, everything works. Unable to save timezone information in java.sql.Timestamp . If you need to remember what time zone you received in the input, save it as a separate column in the database. You can save the correct point in time in java.sql.Timestamp , but not the time zone received in the input. For this you need an extra field.

Since you like the dates of your DB in UTC, you can get data from the database as follows: OffsetDateTime.ofInstant(Instant.ofEpochMilli(tstamp.getTime), ZoneId.of("UTC")) . This will be the right time, but in the UTC time zone. You cannot extract from the DB the fact that OffsetDateTime was in the time zone +0200 before you saved it in the DB, because java.sql.Timestamp does not store the time zone component. If you need this information, you need to save it in a separate column in the database.