Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
desmondchew
Creator III
Creator III

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

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

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

 

Labels (1)
3 Solutions

Accepted Solutions
john_wang
Support
Support

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

john_wang_0-1706277853198.png

Hope this helps.

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

desmondchew
Creator III
Creator III
Author

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

View solution in original post

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

4 Replies
john_wang
Support
Support

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

john_wang_0-1706277853198.png

Hope this helps.

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
desmondchew
Creator III
Creator III
Author

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

SushilKumar
Support
Support

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

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!