11

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?

Community
  • 1
  • 1
Alberto Coletta
  • 1,563
  • 2
  • 15
  • 24

2 Answers2

13

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.

radumanolescu
  • 4,059
  • 2
  • 31
  • 44
  • This is a good answer, but do you think we should call `Timestamp.toInstant()` directly? Your code above calls: `Instant.ofEpochMilli(tstamp.getTime)`. As I understand, we would lose precision below milliseconds. – kevinarpe May 17 '21 at 10:02
  • 1
    This answer is not quite correct. And I would caution explaining it this way. There is nothing about a java.sql.Timestamp / java.util.Date that is correlated to a timezone, even implicitly. UTC is a timezone and an epoch does not concern itself with a timezone, period. Please do not think of an epoch being "implicitly UTC". You can use `OffsetDateTime.ofInstant` with any timezone against an instant and it will be the "correct point in time", i.e. the underlying epoch does not change. – taylorcressy Mar 30 '22 at 15:24
  • @taylorcressy "the specified number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT." from https://docs.oracle.com/javase/8/docs/api/java/util/Date.html – radumanolescu Apr 01 '22 at 01:18
  • 1
    @radumanolescu Yes that is understood. But you are conflating things here and misinterpreting the documentation. That is a timezone-based reference to when "the clock" started. It is also the specified number of milliseconds since 31 December 1969 16:00:00 GMT-08:00. The epoch is an instant in time. There is nothing implicit about it being UTC. You could just as easily say that "it's implicitly in PST". You would get the same effect. – taylorcressy Apr 01 '22 at 16:04
  • 1
    `OffsetDateTime.ofInstant(Instant.ofEpochMilli(tstamp.getTime), ZoneId.of("UTC"))` and `OffsetDateTime.ofInstant(Instant.ofEpochMilli(tstamp.getTime), ZoneId.of("America/Los_Angeles"))` are both correct. They are both valid ways to retrieve the date from the db. It all depends on the timezone-based representation that the application needs. Your explanation conveys a false understanding to what a java.util.Date (epoch) truly is. – taylorcressy Apr 01 '22 at 16:05
13

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.

Alvaro Carrasco
  • 6,103
  • 16
  • 24