Bug / Incorrect behavior with Convert_TZ() in MagicETL

zaclingen_zips

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.

MichelleH

@zaclingen_zips This sounds like a mis-alignment with your instance time zone from your company settings. Does the dataflow produce any different results if you set the time zone in the Dataflow Transform settings instead?

Here is a Knowledge base article about troubleshooting time zones in Domo: https://domo-support.domo.com/s/article/360043439073?language=en_US