Bug / Incorrect behavior with Convert_TZ() in MagicETL

TL;DR - The covert_tz() is subtracting an additional 5 hours when trying to convert a date/time value in UTC back to its original ET.

I have a record that occurred on July 31, 2023 at 08:00:29pm, when recorded in UTC this is '2023-08-01T00:00:29.000Z'. This record has 'America/New_York' as a value in timezone . I wanted to convert it back to the original timezone so that it was included in July's reporting. Below is my implementation within MagicETL without column names:

Convert_TZ(2023-08-01T00:00:29.000Z, +00:00, America/New_York)

That function returns: '2023-07-31T15:00:29.000Z' or July 31, 2023 3:00:29pm but all logic based on reading documentation and consulting my good friend ChatGPT says that it should return '2023-07-31T20:00:29.000Z' or July 31, 2023 8:00:29pm (which is when the record actually occurred).

I eventually got my desired result with the following code: CONVERT_TZ(completeDate,'+00:00','+01:00')

According to the documentation at https://www.w3resource.com/mysql/date-and-time-functions/mysql-convert_tz-function.php , that previous line of code should return 2023-08-01T01:00:29.000Z.


Best Answer