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

Question

I'm working on a Scala project and I need to map OffsetDateTime type to SQL Timestamp type. In DB I would like to have UTC times.

The conversion from OffsetDateTime to Timestamp is straightforward (hint from this question) and it 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, timezone is removed and the Timestamp is two hours back in time (UTC), great!

Converting back Timestamp to OffsetDateTime isn't working as expected:

OffsetDateTime.ofInstant(Instant.ofEpochMilli(tstamp.getTime), ZoneId.systemDefault())

// java.time.OffsetDateTime = 2017-04-04T19:46:33.567+02:00

Timezone has been added to the newly created OffsetDateTime, but the time is not correct (it's still UTC, I need that it is adapted to the actual timezone).

Why? What am I doing wrong?

Answer 1

Although java.sql.Timestamp stores the epoch millis, the .toString method uses the default timezone to render the string. Also, the .valueOf interprets the LocalDateTime using your default timezone.

The combination of both things, causes the first conversion to "look" correct, but it is in fact wrong. The value "2017-04-04 19:46:33.567" is being shown in your default TZ, not UTC.

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

Here is 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

The accepted answer to the referenced stackoverflow question is wrong.

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

Answer 2

java.sql.Timestamp is a thin wrapper around a long value representing milliseconds since the epoch (1970-01-01T00:00:00.000 UTC) - so the UTC timezone is implicit in java.sql.Timestamp. It cannot store any timezone info, but implicitly it's in UTC, and as long as everyone knows that, it all works. There is no way to store timezone info in a java.sql.Timestamp. If you need to remember what timezone you received in your input data, save it as a separate column in the DB. You are able to save a correct moment in time in java.sql.Timestamp - but not the timezone received in the input data. For that you need an extra field.

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