Infrastruktur & Datenbanktechnologie08. März 2023Tom Lieber

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')

SQL Server Result

Compare – 2nd row and last Row

Exasol Result

Now the question arises:

Do we have outdated JDBC drivers?

JDBC Driver not really up2date..

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?!

„The exact String is: -etlJdbcJavaEnv=-Duser.timezone=Europe/Vienna“

You barely change this setting and reboot the machine:

USE UTC !

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

YES!

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;

Postgres Query Result

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

OMG – in EXASOL -1
and -2 diffs?

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