2

I have an application that works with JPA and stores date and time using UTC. I am adding support for MS SQL Server and not sure how to store time. The standard datetime/datetime2 returns data in local timezone only and not UTC.

I considered using datetimeoffset SQL Server data type, but instead of the standart java.sql.Timestamp it returns a proprietary object, incompatible with other databases I am working with.

The only semi-working solution that I found is to force JVM timezone to be UTC (e.g. TimeZone.setDefault(TimeZone.getTimeZone("UTC"))). It causes MS SQL Server driver to return time in UTC for the datetime/datetime2 data type.

Is there a better a solution?

Sergey
  • 3,253
  • 2
  • 33
  • 55
  • 1
    See also https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific – Mark Rotteveel Feb 12 '18 at 15:02
  • @MarkRotteveel I am using JPA AttributeConverter, so, unfortunately, this answer does not help. I saw that there is a Calendar object inside java.sql.Timestamp, but it is inaccessible without resorting to reflection. – Sergey Feb 13 '18 at 08:00

1 Answers1

0

There is a way to get UTC time from datetime/datetime2 MS SQL Server data type which also does not break other databases.

The solution is to convert java.sql.Timestamp read from DB to Instant using the following code: sqlTimestamp.toLocalDateTime().toInstant(ZoneOffset.UTC)

See https://github.com/Microsoft/mssql-jdbc/issues/339 for a more in-depth explanation.

Sergey
  • 3,253
  • 2
  • 33
  • 55