Using ZonedDateTime with datetimeoffset in SQLServer

user_346666 0
Viewed 4485

I have an application that uses a SQLServer database with datetimeoffset columns. Preferably, I would like to use ZonedDateTime as my JPA entity object type like so:

@Column(name = "CreateTimestamp", nullable = false)
ZonedDateTime createTimestamp;

However, the timezone is not being properly saved. No matter what I set the ZoneId to in the ZonedDateTime, the value will always be in GMT in the database.

Looking at Microsoft's JDBC mapping diagram, the recommended class to use is microsoft.sql.DateTimeOffset which is not what I would prefer.

I've explored using a @Converter to map between the two, but having to provide special knowledge like this seems like a red flag that I'm doing something wrong - especially since I need this to work inside of an integration test using H2.

What is the best way to use datetimeoffset and ZonedDateTime in a vendor agnostic way?

jpa sql-server java
2 Answers

A bit of a gotchya here. The data time ZoneDateTime contains more information than an OffsetDateTime. The zone. Mind you, a zone is not the same as an offset. It is more. Two zones could, at a given moment, have the same offset, but months later, one zone "springs forward" the other zone does not. Or, they spring forward on different days. In converting a string from an OffsetDateTime to a ZoneDateTime, you are introducing a zone where one did not exist before.

user_65911520
Related