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.
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!**0
Yes. All are valid0
There are also no null values0
@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"0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 198 Visualize
- 254 Beast Mode
- 2.1K Charting
- 11 Variables
- 82 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 178 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive