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

hamza_123
hamza_123 Contributor
in Magic ETL

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.

Comments

  • GrantSmith
    GrantSmith Coach

    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • hamza_123
    hamza_123 Contributor

    Yes. All are valid

  • hamza_123
    hamza_123 Contributor

    There are also no null values

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories