How to change timezone of a column in Magic ETL based on another column

How do I change timezone of a column in Magic ETL based on the input of another column.

e.g. I have a column called Datetime, which has date-time values (e.g. 2020-12-07T00:00:00). I have another column called Timezone which has a timezone (e.g. 'UTC')

I want to convert the datetime column from America/New_York Timezone to the timezone specified in my timezone column. I use the following formula in beast mode:

CONVERT_TZ(`Datetime`, 'America/New_York', `Timezone`)

However, I get an error saying "An Unknown Error Occurred".

This formula works perfectly well in MySQL dataflow.

Can anyone help me here? Hoe to replicate this formula in a magic ETL.

Find more posts tagged with

Comments

GrantSmith

Hi @hamza_123

Are all of your Timezone values valid? Do you have any null / empty values?

Do you have any null or empty timestamp values?

hamza_123

Yes. All are valid

hamza_123

There are also no null values

jaeW_at_Onyx

@hamza_123 not to be that guy ... but why do you want to do this?

data stored in Domo is assumed to be in UTC by the viz layer.

if you change the timezone in ETL to any other time zone, then the Viz layer will think that it's UTC AND THEN convert the timestamp to your company time ... which will be wrong b/c you adjusted the time time in ETL.

If you want to know what time activity occurred and have the ability to arbitrarily show different timezones, it may make sense to add a column called hourOffsetToEST or hourOffsetToCST instead of having a bunch of timestamp columns.

to calculate it, I recommend building a date dimension wherein for every day, you calculate your hourOffsetToEST and hourOffsetToCST (b'c hour offset is a product of the day of the year).

don't apply this logic in your transactional dataflow. it'll be much harder to validate / reproduce / document / scale.