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.
Comments
-
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!**0 -
Yes. All are valid
0 -
There are also no null values
0 -
@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
Categories
- 7.7K All Categories
- 12 Connect
- 925 Connectors
- 247 Workbench
- 441 Transform
- 1.7K Magic ETL
- 61 SQL DataFlows
- 456 Datasets
- 88 Visualize
- 222 Beast Mode
- 2.1K Charting
- 8 Variables
- 5 Automate
- 349 APIs & Domo Developer
- 85 Apps
- Workflows
- 17 Predict
- 3 Jupyter Workspaces
- 14 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 18 Manage
- 39 Governance & Security
- 46 Product Ideas
- 1.1K Ideas Exchange
- 6 Community Forums
- 19 Getting Started
- 6 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 577 日本支部