When the time is not right

DATETIME(2) – Transfer from SQL Server to Exasol

Customer reports that the time does not arrive correctly from SQL Server to Exasol – basically Daylight Saving Times

SPHINX.AT

He even sends a test case that can be reproduced 1 to 1.

Here is a small excerpt from the preparation:

Sql Server: (Create view for testing)

create view datetimetest as
select dt,convert(varchar,dt, 21) as vardt
from (
select try_convert(DATETIME,'1980-04-05 23:59:59.000',102) as dt 
union all
select try_convert(DATETIME,'1980-04-06 00:00:00.000',102) as dt 
union all
select try_convert(DATETIME,'1980-04-06 00:10:00.000',102) as dt 
union all
select try_convert(DATETIME,'1980-04-06 00:59:00.000',102) as dt 
union all
select try_convert(DATETIME,'1980-04-06 01:00:00.000',102) as dt 
union all
select try_convert(DATETIME,'1980-04-07 00:00:00.000',102) as dt 
union all
select try_convert(DATETIME,'1981-03-29 02:00:00.000',102) as dt 
union all
select try_convert(DATETIME,'1981-03-29 02:01:00.000',102) as dt
) a;

Exasol: (Query the data via Exasol)

SELECT * 
FROM ( IMPORT FROM jdbc at con_mssql STATEMENT 
       'select * from dbo.datetimetest')

Compare – 2nd row and last Row

Now the question arises:

Do we have outdated JDBC drivers?

Download – JDBC Driver for SQL Server | Microsoft Learn

Problem solved?

NOT AT ALL

Why can a date actually become a timestamp?
Data type of SQL Server becomes Timestamp in jdbc driver.
=> Because it converts Java/JDBC!

Which setting must now be set how, so that we take over the DATETIME(2) correctly? It is actually only converted if it does not correspond to the default UTC.

And in ExaOperation we see (not easy to find) the answer:
in Extra Database Parameters the user.timezone is set to Europe/Vienna?!

You barely change this setting and reboot the machine:

Lo and behold Exasol query now returns the same result as SQL Server:

Problem solved?

unfortunately NOT – THE PROBLEM IS NOW AT POSTGRES !
IN CASE OF TIMESTAMP WITH TIME ZONE!

After further analysis we found out that due to a bug with Postgres this setting was set to Europe/Vienna. (from 2020)

Now we have to find out if this bug still exists…

In fact there was or is a bug with Postgres datatype timestamp with TIME ZONE… and lo and behold… the bug is still there almost 3 years later.

Postgres: (create testview in Postgres SQL)

create or replace view datetimetest as
select  dt dtwotz, dt at time zone 'Europe/Vienna' as dt,cast(dt as text) as vardt
from (
select 
cast('1980-04-05 23:59:59.000' as timestamp without time zone) as dt 
union all
select 
cast('1980-04-06 00:00:00.000' as timestamp without time zone) as dt 
union all
select 
cast('1980-04-06 00:10:00.000' as timestamp without time zone) as dt 
union all
select 
cast('1980-04-06 00:59:00.000' as timestamp without time zone) as dt 
union all
select 
cast('1980-04-06 01:00:00.000' as timestamp without time zone)  as dt 
union all
select 
cast('1980-04-07 00:00:00.000' as timestamp without time zone) as dt 
union all
select 
cast('1981-03-29 02:00:00.000' as timestamp without time zone)  as dt 
union all
select 
cast('1981-03-29 02:01:00.000' as timestamp without time zone)  as dt 
) a;

Now still using user.timezone = UTC from Exasol to query on Postgres:

If we switch back to Europe/Vienna we have the problem with SQL Server.

Therefore we have to decide:

• Postgres vs. SQL Server => which system is more important 🙂
• Urge Exasol to fix the bug, it is open since 2020.
• implement workaround => load it into varchar and then convert it into timestamp with TZ (for SQL Server or for Postgres is basically the same)

We are happy to help with such analyses.

Yes! SERGO + YAITCON

Related to the topic:

Daylight saving time 2023: Tips to cope with lost hour of sleep – cleveland.com