0

I'm using spring, mybatis, mssql-jdbc-8.4.1...

I have quite simple test code to save and fetch model from DB.
Model field is of type Instant, DB column is of type datetimeoffset.

After persisting / fetching I have time difference exactly on my timezone offset.
Changing DB column type to datetime2 fixes the problem.

Q: Should I treat this as underlying libraries issues (either mybatis or jbdc driver) or I have wrong expectation of java Instant being saved in UTC timezone and fetched as such to remain valid 'instant'?

Mike
  • 20,010
  • 25
  • 97
  • 140

1 Answers1

4

Changing DB column type to datetime2 fixes the problem.

datetime2 is MS SQL Server type corresponding to ANSI SQL TIMESTAMP type and it maps to LocalDateTime in JDBC 4.2. A LocalDateTime does not hold any timezone information and therefore you will get the same value always.

If you want to store it in such a way that the retrieved value should change automatically in a timezone with a different offset value, you should save it to a database field of type, TIMESTAMP WITH TIMEZONE which corresponds to OffsetDateTime. Note that most JDBC drivers do not support Instant and ZonedDateTime. Also, most of these drivers require the OffsetDateTime instances to be with a timezone offset of +00:00 hours because the backend stores them as UTC.

Check this answer for some more information.

Arvind Kumar Avinash
  • 71,965
  • 6
  • 74
  • 110