Home How to store Timestamp in MS SQL Server with JPA?
Reply: 1

How to store Timestamp in MS SQL Server with JPA?

Sergey Published in 2018-02-12 10:36:05Z

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 Reply to 2018-02-13 16:30:15Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.293864 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO