Convert a UTC timezone to Local Time
Hello, Experts,
I am looking for guidance on how to properly convert time that comes in as UTC format from our MySQL server. I have converted it in the output within the MySQL code to be YYYY-DD-MM for the correct timezone but I want to keep the datetime feel to have HH:MM:SS. Is this possible? If so, how?
Thanks in advance!
Best Answer
-
Take a look at this KB.
I'm 90% positive that if you set this value to UTC AND you ingest the data into Domo, it will store in UTC and DISPLAY according to company settings.
TO TEST THIS
Upload Date, DateTime_inUTC AND UTC_Time as separate columns. Time_UTC should never shift because it will be stored as a string with no concept of 'date'. DateTime_UTC should adjust to DateTime_CompanySettings because that's how our visualization layer works.
REGARDING VALUABLE POS DATA.
it doesn't really make sense to consider POS Data as UTC converted time if you're modeling customer behavior. Sales in hawaii that occurred at midnight UTC doesn't make sense. what matters is what time the shopper thought it was in Hawaii at the time of their purchase. -- that said, for use cases like global demand planning ... that's a different story.
THEREFORE: I would recommend adding an extra column for Local_Time. Which I would achieve with the aforementioned Time Dimension.
@imelendez -- If i'm honest, this is not an area of Domo I've spent a lot of time working on, so... time to fix that! Next week I'll run a hackathon-styled session to solve this problem. You're welcome to join. Register now! If you supply an anonymized dataset with 20 rows of POS data for stores distributed across 3 timezones before and after daylight savings, we'll use your provided data as the input dataset. email me if you're interested: jae.wilson@domo.com
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"1
Answers
-
The knowledgebase has a good article outlining dealing with Time Zones: https://knowledge.domo.com/Administer/Specifying_Company_Settings/033Time_Zone_Issues_FAQ. It outlines several different options to switch timezones with your data.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith I get that, but, I think that has to do with more the company settings. I am just concerned about data that is coming from a data source that is in UTC format and I need to keep the datetime data type but in local time.
0 -
You're in a good spot, actually.
I get very frustrated with how Domo handles datetime data because it makes the assumption that the data coming in is UTC.Assuming your instance has the global setting of a timezone defined, when you bring in the data in Workbench you don't have to do an offset - it will treat it like it is UTC by default.
On the presentation layer, you will see that datetime offset for local time.
(I'm running low on coffee this morning so anyoen else please feel free to correct me.)
0 -
@SeanPT is correct.
As i understand it, unless specified otherwise, Domo will ingest your data and assume it is in the timezone of your company settings. From there it will store the data in the data layer in UTC.
The Visualizations will use company settings to display 'the correct timezone.'
If being able to display different timezones is important i would split date and time into separate columns and have a time and date dimension that allows me to apply transformations to adjust the display to the desire display time_zone.
ultimately, my time dimension would have 'UTC date' and an offset column (number of hours) for different timezones PST, CST, etc. my date dimension would have a binary column (true / false) for if daylight saving offset was applied. then in beast modes, apply a time_add to apply the appropriate math to get PST_Time or CST_Time.
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 -
@SeanBand @jaeW_at_Onyx thanks for the feedback.
So, if I am understanding correctly, you are saying that if mysql is sending the date data to me via the mysql connector as UTC, and I change my company settings to be my timezone instead of default UTC that should solve the problem? My only concern here is that since the data source is our main point of sale web app which has many locations across various states (different timezones) AND that our Domo users across various parts of the county might see their dates as our timezone. Will that just apply to me or to everyone else as well?
0 -
Take a look at this KB.
I'm 90% positive that if you set this value to UTC AND you ingest the data into Domo, it will store in UTC and DISPLAY according to company settings.
TO TEST THIS
Upload Date, DateTime_inUTC AND UTC_Time as separate columns. Time_UTC should never shift because it will be stored as a string with no concept of 'date'. DateTime_UTC should adjust to DateTime_CompanySettings because that's how our visualization layer works.
REGARDING VALUABLE POS DATA.
it doesn't really make sense to consider POS Data as UTC converted time if you're modeling customer behavior. Sales in hawaii that occurred at midnight UTC doesn't make sense. what matters is what time the shopper thought it was in Hawaii at the time of their purchase. -- that said, for use cases like global demand planning ... that's a different story.
THEREFORE: I would recommend adding an extra column for Local_Time. Which I would achieve with the aforementioned Time Dimension.
@imelendez -- If i'm honest, this is not an area of Domo I've spent a lot of time working on, so... time to fix that! Next week I'll run a hackathon-styled session to solve this problem. You're welcome to join. Register now! If you supply an anonymized dataset with 20 rows of POS data for stores distributed across 3 timezones before and after daylight savings, we'll use your provided data as the input dataset. email me if you're interested: jae.wilson@domo.com
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"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive