DSL.currentOffsetDateTime() not returning current instant in SQL Server

[ergonmichelle]

Your question

We ran into a timezone issue when migrating our database from PostgreSQL to MS SQL Server. We use DSL.currentOffsetDateTime() which in SQL Server does not necessarily return an OffsetDateTime equal to the current instant whereas in PostgreSQL it does.

The jOOQ documentation states that DSLContext.currentOffsetDateTime() is translated into CAST(CURRENT_TIMESTAMP AS datetimeoffset) for SQL Server. And the SQL Server documentation states that CURRENT_TIMESTAMP returns the current database system timestamp without the database time zone offset. The cast then adds the offset +00:00 which, in our case, is wrong.

Given the name currentOffsetDateTime() I would have expected the returned OffsetDateTime to be equal to the current instant (not minding the actual offset too much as long as it is equal to the current instant).

Is this a bug or the intended behaviour? Is there another method in DSL which we could use instead?

Below a few sample DB Query results for the different time functions in SQL Server, given a local time in Europe/Zurich of 14:53:15:

• current_timestamp => 2022-08-31 14:53:15.993
• SYSDATETIMEOFFSET() => 2022-08-31 14:53:15.9884308 +02:00
• CAST(CURRENT_TIMESTAMP AS datetimeoffset) => 2022-08-31 14:53:15.9933333 +00:00
• CONVERT([datetimeoffset],getdate()) => 2022-08-31 14:53:15.9933333 +00:00
• CONVERT([datetimeoffset],GETUTCDATE()) => 2022-08-31 12:53:15.9933333 +00:00

The correct instant is returned by SYSDATETIMEOFFSET() and CONVERT([datetimeoffset],GETUTCDATE()).

jOOQ Version

jOOQ Porfessional Edition 3.17.1

Database product and version

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) Nov 2 2020 18:35:09 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Java Version

openjdk version "11.0.8" 2020-07-14 LTS

OS Version

No response

JDBC driver name and version (include name if unofficial driver)

com.microsoft.sqlserver:mssql-jdbc:10.2.0.jre11

[lukaseder]

Thanks a lot for your report.

There are a few related issues, which I plan to investigate soon, so this might be another bug. You can always use plain SQL templating to get the exact native behaviour you require: https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating

There shouldn't be a redundant function for the same thing, currently.