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.