Search code examples
javasql-serverjdbcazure-sql-databasejava-time

What is the SQL Server Database column type for java.time.OffsetDateTime?

We are trying to map OffsetDateTime in Java and insert into SQL Server, using JDBCTemplate. Our customers are in multiple different time zones around the world.

What is the proper SQL Server / Azure SQL data column type for OffsetDateTime? I didn't see it in chart here . https://learn.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver16

using java.time.OffsetDateTime

private OffsetDateTime purchaseDate;

I am thinking MS SQL datetimeoffset, however the table is displaying Java type as microsoft.sql.DateTimeOffset, not java.time.OffsetDateTime .

However, using datetimeoffset in SQL seems to work with java.time.OffsetDateTime, just want to be sure.

enter image description here

Solution
  • Kind of data Standard SQL MS SQL Server Java
    Point on time line TIMESTAMP WITH TIME ZONE datetimeoffset In JDBC, use OffsetDateTime.
    Outside of JDBC, generally best to use Instant or ZonedDateTime.

    To represent a moment, a specific point on the timeline, use the SQL standard type of TIMESTAMP WITH TIME ZONE. In Microsoft SQL Server that type maps to datetimeoffset.

    Java offers three classes to represent a point on the timeline:

    • java.time.Instant
    • java.time.OffsetDateTime
    • java.time.ZonedDateTime

    The first represents a moment as seen with an offset of zero hours-minutes-seconds from the temporal meridian of UTC. The second is similar but can have an offset of any number of hours-minutes-seconds ahead/behind UTC. The third one contains a time zone, which is a named history of the past, present, and future changes to the offset used by the people of a particular region as decided by their politicians.

    The SQL standard barely touches on date-time matters, and does so poorly. The standard lacks the concepts captured in the Instant and ZonedDateTime classes. So JDBC maps only the OffsetDateTime class to a column of the type TIMESTAMP WITH TIME ZONE.

    As for your chart, I’ve no idea what is going on. That chart seems to be referencing this class, microsoft.sql.Types.DATETIMEOFFSET. That class appears to be part of Microsoft’s JDBC driver. But Microsoft defining their own class mapped in JDBC makes no sense as that class would be incompatible with JDBC-compliant programs. I’m not a Microsoft customer, so I cannot provide an explanation.