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

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

ColemenWilson

Yep, we had the same issue @MichelleH pointed out and resolved it using the same suggestion from her above.

zaclingen_zips

https://community-forums.domo.com/main/discussion/comment/73821#Comment_73821

Thanks for the feedback. I get it now.

I feel like it might be worth including that detail in the function's description within MagicETL. This isn't the first time a lack luster description in there has stirred me wrong.