Monat: März 2023 (Page 2 of 2)

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


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)

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

Download – JDBC Driver for SQL Server | Microsoft Learn

Problem solved?


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

SQL datatyp to Java Type

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:


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


Problem solved?


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 (
cast('1980-04-05 23:59:59.000' as timestamp without time zone) as dt 
union all
cast('1980-04-06 00:00:00.000' as timestamp without time zone) as dt 
union all
cast('1980-04-06 00:10:00.000' as timestamp without time zone) as dt 
union all
cast('1980-04-06 00:59:00.000' as timestamp without time zone) as dt 
union all
cast('1980-04-06 01:00:00.000' as timestamp without time zone)  as dt 
union all
cast('1980-04-07 00:00:00.000' as timestamp without time zone) as dt 
union all
cast('1981-03-29 02:00:00.000' as timestamp without time zone)  as dt 
union all
cast('1981-03-29 02:01:00.000' as timestamp without time zone)  as dt 
) a;
Posgres 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.


Related to the topic:

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

Our Top-Technical-Pains

  1. Certificate Handling
  2. LDAP – Active Directory structures
  3. Restricted environments (proxies, VPNs, …) that just get in the way instead of providing additional security
  4. Missing autodocumenter – old Confluence documentation
  5. Case sensitive column names
  6. Language support for tools
  7. Handling of time zones Daylight Saving Time

Exasol one year experience

Exasol and Oracle are both powerful relational database management systems that can be used for different use cases.


Below are what I consider to be the top 5 features of Exasol compared to an Oracle database:

  1. Exasol’s shared-nothing architecture means that each node in the cluster has its own resources such as CPU, RAM and disk space and operates independently of other nodes. This ensures high scalability and resilience, since if a node fails, only its data is affected and not the entire system.
  2. In addition, Exasol provides automatic data distribution and replication to minimize data access time and increase availability. In-memory technology ensures that typically 10-15% of the data is quickly available in memory, while the rest is offloaded to disk. The high level of parallelization and scalability makes it possible to process large amounts of data in a short time, making Exasol a popular solution for data analysis and business intelligence.
  3. Scalability: Exasol can scale horizontally and vertically and thus offers high scalability. Oracle, on the other hand, is not as easy to scale and usually requires more effort.
  4. Easy administration: The administration of Exasol databases is easier than the administration of Oracle databases. Since there is simply not that much to manage 😉 We don’t need to configure and monitor RAC or Data Guard.
  5. Real-time processing: Exasol can process data in real time, enabling real-time analysis and decision making. Oracle also offers this feature, but not at the same speed and efficiency as Exasol.

e.g.: If during the execution of a query it is determined that a certain execution plan is not optimal, the optimizer can generate a new plan and use it to process the query faster and more efficiently. (Adaptive Plan at Oracle)

Another example of dynamic optimization is adaptive indexing. Here the optimizer can decide which indexes are needed to make a query more efficient and create or remove these indexes at runtime. And that is really ingenious!

No light without shadow, but this is also communicated fairly and transparently by Exasol, this database is not suitable for an OLTP system. (SQL parsing and single inserts/updates are much slower than with an Oracle for example). There is also no point-in-time recovery, because there are no archive logs and therefore the last backup and its time are decisive which data can be recovered. And this is hard to argue for a normal OLTP application if this data was lost.

For me as a DBA an Exasol is a dream and as a developer by the integration of Python and R and other Languages anyway incredible.

As an Oracle developer I miss features like the Oracle Scheduler , Oracle VPD and of course the low code tool like Oracle Apex. However, it is possible to combine these two worlds to get the best out of it for our customers.

I also find Oracle PL/SQL more convenient than Exasol LUA. But I would prefer everything to run in Python only 😉


Newer posts »

© 2024 Sphinx Blog

Theme by Anders NorenUp ↑