Convert DATETIME (UTC) to specific timezone

imelendez · April 2021

Hello, Domosapiens!

I have an interesting quandry. I have a data flow (using MagicETL - should I be using something else?) that I am trying to build and convert a date that my MySQL data connector is bringing over.

The datetime column comes from an orders table and is called posted_date and it brings the data over as UTC from the remote server. There is a metadata table that relates to this table that outlines what each location's timezone ID is (i.e: "America/Chicago, America/New_York", etc). Shop and the Orders table relate to each other by a location_ID.

In my ETL, as you would imagine, I am bringing in both input datasets orders and shop and selecting the columns I need, for the sake of example, let us select location ID, posted_date from the orders table and location ID and timezone_ID from the shop table. My next goal is to try to convert each row on the orders table from UTC to the rows given timezone.

ex: schema and rows

In the data flow, I tried using formula object and in it used convert_TZ(posted_date, "utc", `timezone_ID`) and when I run the preview I get an error. Any thoughts on why this would be erroring out? If not, and if you have solved this before in a DF, how did you do this?

I know you could easily convert this in the SQL queries in the data connector but the join is expensive on the DB side and I am trying to avoid doing that and let Domo ETL do this.

I appreciate your thoughts, comments, suggestions in advance, experts!

Humbly,

Isaiah Melendez

MarkSnodgrass · April 2021

The convert_TZ doesn't want the friendly timezone names. It wants the hours you want to move from to and should look like this:

CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00')

You could create a simple lookup table that would have your timezone id name and have the number of hours from UTC that it is as another column and join that in your ETL, then use that offset in your convert_tz formula.

You could also create a case statement in your current formula tile to do the offset based on the timezone id.

MarkSnodgrass · April 2021

The convert_TZ doesn't want the friendly timezone names. It wants the hours you want to move from to and should look like this:

CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00')

You could create a simple lookup table that would have your timezone id name and have the number of hours from UTC that it is as another column and join that in your ETL, then use that offset in your convert_tz formula.

You could also create a case statement in your current formula tile to do the offset based on the timezone id.

imelendez · April 2021

Wow very insightful. Now, a follow-up question, I assume when the time changes according to Daylights Savings Time I need to account for this, or should the offset be enough?

MarkSnodgrass · April 2021

Good question. If your source data is level-setting to UTC, than it seems like you would be okay, but I would do some test on that. You could build the logic into your formula tile if you do need to adjust for it because you can use the MONTH function and the DAY function to determine if you are in the daylight savings time window or not.

jaeW_at_Onyx · April 2021

@imelendez :P you posted two threads about the same thing!

ok. extend your timezone_id table to include a column "offset_from_utc" when not under the impact of daylight savings.

then build a date dimension with the binary isDST. then you can do the adjustments i described in the other post.

Convert DATETIME (UTC) to specific timezone

Best Answer

Answers

Categories