java.time.OffsetDateTime fields to MS SQLServer datetimeoffset column using Hibernate

Question

We have Date fields with type java.time.OffsetDateTime (JAVA8) in our models. We are using Microsoft SQL Server as our database. And Table columns in database are of type datetimeoffset. We are using Hibernate as ORM in our application. But we are not able to store our java.time.OffsetDateTime fields to sql server datetimeoffset column using Hibernate.

Please let us know how can we achieve this?

Answer 1

Adding the following converter class worked for me. The 2nd formatter might be due to hitting an old SQL Server database or old data, I'm not sure, so you might not need it:

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

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

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

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

        try {
            return OffsetDateTime.parse(dbData, MAIN_FORMATTER);
        } catch (DateTimeParseException e) {
            // Some old? data has only 7 digits for nanoseconds
            return OffsetDateTime.parse(dbData, SECONDARY_FROM_DB_FORMATTER);
        }
    }
}