Map MS SQL Server DateTimeOffset to Java 8 OffsetDateTime?

I want to use column with SQL type datetimeoffset as Java 8 OffsetDateTime.

With latest MS JDBC driver there is support for JDBCType.TIMESTAMP_WITH_TIMEZONE:

create table temp (
  id int IDENTITY(1,1),
  ts datetimeoffset,
  CONSTRAINT temp_pk PRIMARY KEY (id)
);

    @Test
    public void temp_jdbc_insert() throws SQLException {
        final Connection conn = ds.getConnection();
        final PreparedStatement statement = conn.prepareStatement("insert into temp values (?)");
        final OffsetDateTime ts = OffsetDateTime.parse("2021-02-03T00:00:00-02:00");
        // Types.TIMESTAMP_WITH_TIMEZONE;
        statement.setObject(1, ts, JDBCType.TIMESTAMP_WITH_TIMEZONE);
        statement.execute();
        log.info("Inserted: {}", statement.getUpdateCount());
        statement.close();
    }

    @Test
    public void temp_jdbc_select() throws SQLException {
        final Connection conn = ds.getConnection();
        final PreparedStatement statement = conn.prepareStatement("select ts from temp where id = 11");
        statement.execute();
        final ResultSet rs = statement.getResultSet();
        rs.next();
        final Object obj = rs.getObject(1);
        // obj: 2021-02-03 00:00:00 -02:00, type: class microsoft.sql.DateTimeOffset
        log.info("obj: {}, type: {}", obj, obj.getClass());
        statement.close();
    }

However when I try to use Hibernate with mapping:

@Entity
@Getter @Setter
@Accessors(chain = true)
@ToString
public class Temp {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private OffsetDateTime ts;
}

Hibernate damages dates on save:

    @Transactional
    @Commit
    @Test
    public void temp() {
        Temp temp = new Temp().setTs(OffsetDateTime.parse("2021-01-05T00:00:00+00:00"));
        em.persist(temp);
        // Will be  2021-01-05 02:00:00 +00:00 if you query it in the DB.
        log.info("tmp: {}", temp);
    }

I searched for the string TIMESTAMP_WITH_TIMEZONE Hibernate sources and cannot find it. Basically Hibernate lacks full suport of JDBC 4.2 features.

It looks like I can toy with AbstractSingleColumnStandardBasicType and SqlTypeDescriptor and JavaTypeDescriptor, for example using DbAssist/DbAssist-5.2.2/src/main/java/com/montrosesoftware/dbassist/types/UtcDateType.java at master · montrosesoftware/DbAssist · GitHub as an example but I feel it is too complicated.

I tried to implement AttributeConverter<java.time.OffsetDateTime, microsoft.sql.DateTimeOffset> (+ @Converter(autoApply = true)) and it failed with:

com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetimeoffset is not allowed. Use the CONVERT function to run this query.

I think it can be explained by preceding log statement:

binding parameter [1] as [VARBINARY] - [2021-01-05 00:00:00 +00:00]

Any suggestions to have OffsetDateTime properly working?

My goal is to avoid TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC")); and other UTC voodoo: if I put something to the DB I like to be sure I read back the same instant…

I see a trick with String here: java - Using ZonedDateTime with datetimeoffset in SQLServer - Stack Overflow

With AttributeConverter<ZonedDateTime, String> the parameter will be passed as varchar and DB or JDBC driver take care of datetime + TZ offset.

Also some references:

[HHH-13369] OffsetDateTime fields persisted incorrectly with SQL Server DATETIMEOFFSET - Hibernate JIRA (OPEN)
OffsetDateTime fields persisted incorrectly with SQL Server DATETIMEOFFSET

Add support for OffsetDateTime to be passed as 'type' in ResultSet.getObject() by harawata · Pull Request #830 · microsoft/mssql-jdbc · GitHub (IMPLEMENTED)
Add support for OffsetDateTime to be passed as ‘type’ in ResultSet.getObject() #830

This is something that will be supported with Hibernate 6. In the meantime you can use a custom type.

1 Like

The custom type will pollute entities with extra annotations. I thought about registering some “convertor” to avoid edits on entities. Also we need interoperability with H2 (for in-memory tests).

Awaiting GA of Hibernate 6! Is there a task or bug report regarding support of OffsetDateTime to follow the progress & design?

You should be able to simply register a custom basic type, no need for extra annotations. A MetadataContributor should be fine for this purpose.

Is there a task or bug report regarding support of OffsetDateTime to follow the progress & design?

The best thing I can offer you is this: Support timestamp with timezone/offset · hibernate/hibernate-orm · Discussion #4201 · GitHub

1 Like

Today I managed to make it works without marking anything by @Type(type = XLocalDateTime) & @TypeDef.

My solution is inspired by:

I define usual Hibernate 5 boilerplate:

public class OffsetDateTimeJavaTypeDescriptor extends AbstractTypeDescriptor<OffsetDateTime> {
    public static final OffsetDateTimeJavaTypeDescriptor INSTANCE = new OffsetDateTimeJavaTypeDescriptor();

    public OffsetDateTimeJavaTypeDescriptor() {
        super(OffsetDateTime.class);
    }

    @Override
    public OffsetDateTime fromString(String str) {
        if (str == null) { return null; }
        return OffsetDateTime.parse(str);
    }

    @SuppressWarnings({"unchecked"})
    @Override
    public <X> X unwrap(OffsetDateTime value, Class<X> type, WrapperOptions options) {
        if (value == null) {
            return null;
        } else if (OffsetDateTime.class.isAssignableFrom(type)) {
            return (X) value;
        } else if (ZonedDateTime.class.isAssignableFrom(type)) {
            return (X) value.atZoneSameInstant(ZoneOffset.systemDefault());
        } else if (String.class.isAssignableFrom(type)) {
            return (X) value.toString();
        }
        throw unknownUnwrap(type);
    }

    @Override
    public <X> OffsetDateTime wrap(X value, WrapperOptions options) {
        if (value == null) {
            return null;
        } else if (value instanceof OffsetDateTime) {
            return (OffsetDateTime) value;
        } else if (value instanceof ZonedDateTime) {
            return ((ZonedDateTime) value).toOffsetDateTime();
        } else if (value instanceof String) {
            return fromString((String) value);
        }
        throw unknownWrap(value.getClass());
    }
}

public class OffsetDateTimeMsSqlTypeDescriptor implements SqlTypeDescriptor {
    public static final OffsetDateTimeMsSqlTypeDescriptor INSTANCE = new OffsetDateTimeMsSqlTypeDescriptor();

    public OffsetDateTimeMsSqlTypeDescriptor() {}

    @Override
    public int getSqlType() {
        return Types.TIMESTAMP_WITH_TIMEZONE;
    }

    @Override
    public boolean canBeRemapped() {
        return true;
    }

    @Override
    public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>(javaTypeDescriptor, this) {
            @Override
            protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
                if (value instanceof OffsetDateTime) {
                    st.setObject(index, value, JDBCType.TIMESTAMP_WITH_TIMEZONE);
                }
//                else if (value instanceof Calendar) {
//                    st.setTimestamp(index, timestamp, (Calendar) value);
//                } else if (options.getJdbcTimeZone() != null) {
//                    st.setTimestamp(index, timestamp, Calendar.getInstance( options.getJdbcTimeZone() ) );
//                } else {
//                    st.setTimestamp(index, timestamp);
//                }
            }

            @Override
            protected void doBind(CallableStatement st, X value, String name, WrapperOptions options)
                    throws SQLException {
//                final Timestamp timestamp = javaTypeDescriptor.unwrap(value, Timestamp.class, options);
                if (value instanceof OffsetDateTime) {
                    st.setObject(name, value, JDBCType.TIMESTAMP_WITH_TIMEZONE);
                }
//                else if (value instanceof Calendar) {
//                    st.setTimestamp(name, timestamp, (Calendar) value);
//                } else if (options.getJdbcTimeZone() != null) {
//                    st.setTimestamp( name, timestamp, Calendar.getInstance( options.getJdbcTimeZone() ) );
//                } else {
//                    st.setTimestamp( name, timestamp );
//                }
            }
        };
    }

    @Override
    public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>(javaTypeDescriptor, this) {
            @Override
            protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
                final DateTimeOffset msTs = rs.getObject(name, DateTimeOffset.class);
                return javaTypeDescriptor.wrap(msTs.getOffsetDateTime(), options);
//                return options.getJdbcTimeZone() != null ?
//                        javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
//                        javaTypeDescriptor.wrap( rs.getTimestamp( name ), options );
            }

            @Override
            protected X doExtract(CallableStatement st, int index, WrapperOptions options) throws SQLException {
                final DateTimeOffset msTs = st.getObject(index, DateTimeOffset.class);
                return javaTypeDescriptor.wrap(msTs.getOffsetDateTime(), options);
//                return options.getJdbcTimeZone() != null ?
//                        javaTypeDescriptor.wrap( statement.getTimestamp( index, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
//                        javaTypeDescriptor.wrap( statement.getTimestamp( index ), options );
            }

            @Override
            protected X doExtract(CallableStatement st, String name, WrapperOptions options) throws SQLException {
                final DateTimeOffset msTs = st.getObject(name, DateTimeOffset.class);
                return javaTypeDescriptor.wrap(msTs.getOffsetDateTime(), options);
//                return options.getJdbcTimeZone() != null ?
//                        javaTypeDescriptor.wrap( statement.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
//                        javaTypeDescriptor.wrap( statement.getTimestamp( name ), options );
            }
        };
    }
}

/**
 * Replacement for {@link org.hibernate.type.OffsetDateTimeType}
 * to save OffsetDateTime as MS SQL server type "datetimeoffset".
 */
public class OffsetDateTimeSingleColumnType extends AbstractSingleColumnStandardBasicType<OffsetDateTime> {
    public static final OffsetDateTimeSingleColumnType INSTANCE = new OffsetDateTimeSingleColumnType();

    public OffsetDateTimeSingleColumnType() {
        super(OffsetDateTimeMsSqlTypeDescriptor.INSTANCE, OffsetDateTimeJavaTypeDescriptor.INSTANCE);
    }

    @Override
    public String getName() {
        return OffsetDateTime.class.getCanonicalName();
    }
}

And some Spring Boot magic to register above types in the Hibernate (the key is EntityManagerFactoryBuilderImpl.TYPE_CONTRIBUTORS here):

@Bean
public HibernatePropertiesCustomizer customHibernateTypeRegistrar() {
    return (Map<String, Object> props) -> {
        props.put(
                EntityManagerFactoryBuilderImpl.TYPE_CONTRIBUTORS,
                (TypeContributorList) () -> Arrays.asList((TypeContributor) (typeContributions, serviceRegistry) -> {
                    // Deregister built-in org.hibernate.type.OffsetDateTimeSingleColumnType as it hides our mapping.
                    final BasicTypeRegistry basicTypeRegistry = typeContributions.getTypeConfiguration().getBasicTypeRegistry();
                    Class<OffsetDateTime> clazz = OffsetDateTime.class;
                    basicTypeRegistry.unregister(clazz.getName());
                    basicTypeRegistry.unregister(clazz.getSimpleName());

                    typeContributions.contributeSqlTypeDescriptor(OffsetDateTimeMsSqlTypeDescriptor.INSTANCE);
                    typeContributions.contributeJavaTypeDescriptor(OffsetDateTimeJavaTypeDescriptor.INSTANCE);
                    typeContributions.contributeType(OffsetDateTimeSingleColumnType.INSTANCE);
                }));
    };
}

Default definitions shadow my customization. BasicTypeRegistry.unregister() does the job )) I get an idea from TRACE logs:

01:43:38.851 DEBUG [    Test worker] org.hibernate.type.BasicTypeRegistry 
Adding type registration OffsetDateTime -> org.hibernate.type.OffsetDateTimeType@450352d9
01:43:38.851 DEBUG [    Test worker] org.hibernate.type.BasicTypeRegistry 
Adding type registration java.time.OffsetDateTime -> org.hibernate.type.OffsetDateTimeType@450352d9

The Spring configuration should be adapted to work with H2, otherwise this solution looks solid till Hibernate 6 arrives.

1 Like