topic Re: Why is a date datatype replicated as datetime in MySQL target endpoint in Qlik Replicate

Why is a date datatype replicated as datetime in MySQL target endpoint

desmondchew — Fri, 26 Jan 2024 13:47:54 GMT

We are replicating tables from Oracle 11g into MySQL 8.0. We found that tables with "date" datatype columns in Oracle are replicated as "datetime" in MySQL target endpoint.

According to the documentation here--> https://help.qlik.com/en-US/replicate/November2023/Content/Replicate/Main/MySQL/mysql_db_target_data_types.htm#ar_mysql_902766750_1421922

It says that the date datatype should replicate as date. But it become datetime as default under the transformation.

Re: Why is a date datatype replicated as datetime in MySQL target endpoint

john_wang — Fri, 26 Jan 2024 14:04:43 GMT

Hello @desmondchew ,

There are several options to control the behavior. The easiest way is using a Global Transformation to convert datetime to date, as:

Convert data type
	for %.% with column % and data type DATETIME
	to data type DATE

Hope this helps.

John.

Re: Why is a date datatype replicated as datetime in MySQL target endpoint

desmondchew — Mon, 29 Jan 2024 07:42:30 GMT

Hi John,

Thank you I will try on the solution give. What I am actually asking is why is the datatype date replicate as "datetime" in MySQL when the document says it should be "date"?

Desmond

Re: Why is a date datatype replicated as datetime in MySQL target endpoint

SushilKumar — Mon, 29 Jan 2024 10:38:01 GMT

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

Re: Why is a date datatype replicated as datetime in MySQL target endpoint

john_wang — Mon, 29 Jan 2024 12:39:40 GMT

Hello Desmond @desmondchew ,

Good question.
This is how Replicate designed to make sure the DATE in Oracle can be hold completely in MySQL.

DATE in Oracle is stored in 7 bytes, the first 4 bytes presents date, the last 3 bytes presents time. Although the default format of DATE in Oracle shows as DD-MON-YY but it actually have time part. The DATE presents pretty long period, eg from BC 4712.01.01.

DATE in MySQL is stored in 3 bytes. And there are DATE and DATETIME datatypes in MySQL. From storage size, The match datatype DATE in Oracle should be map to DATETIME in MySQL, otherwise some values overflows in MySQL. So that depends on the precision you want. If you prefer the precision to date, then DATE type in MySQL is good enough. If you want precision set to seconds, then DATETIME type is needed in MySQL.

thanks,
John.