CODEWITHSUNDEEP
javasql-serverhibernate
Basil Vandegriend
Basil Vandegriend

Reputation: 485

Hibernate not correctly storing ZonedDateTime as datetimeoffset in SQL Server

I want to use a datetimeoffset column in SQL Server 2008 that stores both the date & time plus the timezone.

The DDL definition of the column in the database:

LastUpdatedDateTime datetimeoffset DEFAULT SysDateTimeOffset()

The Java definition of the field on my entity mapped using Hibernate:

private ZonedDateTime lastUpdatedDateTime = ZonedDateTime.now();

I'm using Hibernate 5.1.0.Final (via Spring boot 1.3.2.RELEASE) and including org.hibernate:hibernate-java8.

Observed behavior (by querying database using WinSQL): Insert data via a SQL insert statement results in storing the correct date&time and correct timezone: 2016-03-03 13:41:17.5358944 -07:00

Insert data via saving Java entity (with field initialized as per Java code fragment above). Java reports the date/time value (before save) as: 2016-03-04T14:18:17.076-07:00[America/Denver]

After the save, WinSQL reports the value stored in the database as: 2016-03-04 14:18:17.0760000 +00:00

This has the same date&time, but the wrong timezone (UTC rather than -07:00).

When I declared the field in Java using Timestamp instead of ZonedDateTime, I got the same behavior.

How do I get the timezone to be correctly stored? I don't really care if it is stored as UTC or -07:00 time zone as long as the time is correct based on the time zone. I would think that Hibernate would provide support for this (in the hibernate-java8 library) and that I wouldn't have to code a custom converter or custom user data type.

Upvotes: 2

Views: 6856

Answers (1)

Basil Vandegriend
Basil Vandegriend

Reputation: 485

I finally found a solution:

  1. Use java.time.OffsetDateTime instead of ZonedDateTime for the entity field. As per the class Javadoc, OffsetDateTime is intended for use in database persistence.

  2. Revert back to Hibernate 4 (due to other issues I was having). So I don't know if the next steps are necessary if you are using Hibernate 5 with the hibernate-java8 library.

  3. Add a Hibernate Converter from OffsetDateTime to String. Apparently the datetimeoffset column is being treated by default by JDBC as a String (and not a microsoft.sql.DateTimeOffset class as suggested by the Microsoft JDBC driver documentation). The logic in this converter had to deal with the complication that SQL Server only stores 7 digits for nanoseconds while OffsetDateTime provides 9.

  4. Ensure the Converter is included in the Hibernate EntityManager.

The details on these steps are as follows:

DDL column definition is unchanged.

Entity field definition:

private OffsetDateTime lastUpdatedDateTime;

Converter class:

@Converter(autoApply = true)
public class OffsetDateTimeConverter implements AttributeConverter<OffsetDateTime, String> {

    private static DateTimeFormatter FORMATTER_FROM_DB = DateTimeFormatter.ofPattern(
      "yyyy-MM-dd HH:mm:ss.nnnnnnn xxx");
    private static DateTimeFormatter FORMATTER_TO_DB = DateTimeFormatter.ofPattern(
      "yyyy-MM-dd HH:mm:ss.nnnnnnnnn xxx");

    @Override
    public String convertToDatabaseColumn(OffsetDateTime attribute) {
        if (attribute == null) {
            return null;
        }
        return attribute.format(FORMATTER_TO_DB);
    }

    @Override
    public OffsetDateTime convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            return null;
        }
        return OffsetDateTime.parse(dbData, FORMATTER_FROM_DB);
    }
}

Upvotes: 9

Related Questions