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

[AdamS632]

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?

[Anonymous]

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]

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!