3
I need my system to store dates (date and time) with time zone (time zone). The system needs to support multiple databases, so far SQL Server and Postgresql.
With Postgresql the time zone information is saved perfectly, without any problem, already with SQL Server the time zone information is always lost when persisting.
The application runs in Java 8 with JPA 2.2 then the new package classes java.time should work properly, in my understanding. I make use of the class java.time.OffsetDateTime in mapping my entities. Following example:
@Column(name="DT_ACESSO")
private OffsetDateTime dataAcesso;
Persistence/Reading is being done in the JPA standard, that is, using EntityManager.
In SQL Server the date field is of type DATETIMEOFFSET already in Postgresql is TIMESTAMP WITH TIME ZONE.
In SQL Server when persisting the date is always converted to the time zone of the machine that is running, but the information of the zone is lost, always being the value "+00" (UTC). whereas the test is carried out on the following date 2019-08-01 09:00:00 (-03) (time zone Brazil), follow examples:
Information recorded in the database when creating a default date (OffsetDateTime.now()) in time zone "-03":
2019-08-01 09:00:00.0000000 +00:00
Information recorded in database when creating date in different time zone (OffsetDateTime.now(ZoneId.of("Australia/Sydney"))) in time zone "+10":
2019-08-01 09:00:00.0000000 +00:00
As you can see, saving the date is always converted to the local time zone but the time zone information is lost, leaving the time zone in UTC ("+00").
When the application reads these database dates, considering local time ("-03"), the following date is displayed when printing on the console: 2019-08-01 T06:00:00.000-03:00.
As already said in Postgresql the date is correctly persisted.
I am using SQL Server 2017 and Postgresql 11.
Follow information from the application:
- Spring Boot 2.1.6.RELEASE.
- sql server mssql-jdbc-6.4.0.jre8 driver (standard spring boot version)
- Hibernate 5.3.10.Final (standard spring boot version)
- spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Sqlserver2012dialect
I upgraded the sql server driver to version 7.2.2.jre8 but did not resolve the issue.
I don’t do much with SQL Server, but I suggest seeing in the documentation which database type is best suited for
OffsetDateTime. To use the classes ofjava.time, the driver has to be compliant with JDBC >= 4.2, but still each database works differently according to the class of thejava.timeand the type chosen for the column (for example, it may be that the combinationOffsetDateTimewithDATETIMEOFFSETdoes not work, and perhaps the documentation suggests another type, etc...)– hkotsubo
According to this microsoft website (https://docs.microsoft.com/pt-br/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-2017) version 6.4 is compatible with JDBC 4.2. I’m going to look up something else regarding the Offsetdatetime class. Thanks for the feedback.
– Welmau
Being compatible with JDBC 4.2 is not enough. As far as I know, no bank has full support for all classes of
java.time(o postgres, for example, only supports a few). I couldn’t find a table similar to this for SQL Server, but probably or it doesn’t supportOffsetDateTime, or supports but has to use another type that does notDATETIMEOFFSET(or maybe you have some other mechanism/configuration/gambiarra to be done). Sorry I can’t help you more, I really don’t know SQL Server so well...– hkotsubo
@hkotsubo I believe I found a way around the problem. Whereas time zone information is being lost, I have configured Hibernate to use UTC by default. Now Hibernate always converts the date to UTC before sending to the database, "bypassing" the problem in SQL Server. Follows configuration:
spring.jpa.properties.hibernate.jdbc.time_zone=UTC– Welmau