Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
AdamS632
Contributor II
Contributor II

MySQL dates are shifted back a day due to daylights savings time

I am new to Talend and working on moving data from a Microsoft Sql Server to MySQL. I am using tmap to map fields, everything is fine except my datetime/date fields. When the data is loaded to MySQL during daylights savings time (mid Mar-Nov) my dates are shifted back 1 day. All times in the SQL Server DB are stored as 00:00:00 so the behavior is understandable, but I am struggling for a solution.

ex:

SQL Server datetime: 2020-10-28 00:00:00

MySQL datetime: 2020-10-27 23:00:00

MySQL date: 2020-10-27

 

I know its related to my MySQL server settings or the load, but am struggling to identify the issue, Both DBs have a timezone of EST. In MySQL I only need the date part (field is a date) and when testing just the date part I still get the 2020-10-27 date. I am not sure if there function in Talend that can remove the timepart earlier in the process so just the date is sent to

MySQL which might overcome the issue?

Labels (4)
2 Replies
Anonymous
Not applicable

Hi

Take a look at this topic and try the solution: Open the Run Job viewer, click on 'Advanced settings' panel and check 'use JVM parameter' box, add the following parameter.

-Duser.timezone=UTC

 

Let me know if it works.

 

Regards

Shong

AdamS632
Contributor II
Contributor II
Author

Hi Shong,

 

The -Duster.timezone=UTC did not work as it shifted the time:

SQL Server datetime: 2020-10-28 00:00:00

MySQL datetime: 2020-10-27 19:00:00

MySQL date: 2020-10-27

 

But when I set it -Duster.timezone=EST it worked perfectly. Will do some additional testing, but this pointed me in the right direction.

Thanks so much!