# Map MS SQL Server DateTimeOffset to Java 8 OffsetDateTime?

**URL:** <https://discourse.hibernate.org/t/map-ms-sql-server-datetimeoffset-to-java-8-offsetdatetime/5937>\
**Category:** Hibernate ORM\
**Created:** [December 28, 2021, 10:12pm UTC](https://discourse.hibernate.org/t/map-ms-sql-server-datetimeoffset-to-java-8-offsetdatetime/5937 "2021-12-28T22:12:35Z")\
**Posts on this page:** 1\
**Showing post:** 7

<div class="post-metadata">

**Author:** ![gavenkoa](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/gavenkoa/32/537_2.png) [@gavenkoa](https://discourse.hibernate.org/u/gavenkoa)\
**Post date:** [January 3, 2022, 11:45pm UTC](https://discourse.hibernate.org/t/map-ms-sql-server-datetimeoffset-to-java-8-offsetdatetime/5937/7 "2022-01-03T23:45:53Z")

</div>

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

My solution is inspired by:

- [java - How should I register custom Hibernate 5 data type (BasicType) when Spring Data is used? - Stack Overflow](https://stackoverflow.com/questions/42695339/how-should-i-register-custom-hibernate-5-data-type-basictype-when-spring-data)
- [hibernate-types/hibernate-types-55/src/main/java/com/vladmihalcea/hibernate/type/basic at master · vladmihalcea/hibernate-types · GitHub](https://github.com/vladmihalcea/hibernate-types/tree/master/hibernate-types-55/src/main/java/com/vladmihalcea/hibernate/type/basic)

I define usual Hibernate 5 boilerplate:

```auto
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):

```auto
@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:

```auto
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.

---

_[View the full topic](https://discourse.hibernate.org/t/map-ms-sql-server-datetimeoffset-to-java-8-offsetdatetime/5937)._