Convert a UTC timezone to Local Time

imelendez

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!

jaeW_at_Onyx

https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Workbench_5/Setting_the_Time_Zone_for_a_Workbench_5_Job

 

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

GrantSmith

@imelendez

 

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.

imelendez

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

SeanPT

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

jaeW_at_Onyx

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

 

imelendez

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

jaeW_at_Onyx

https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Workbench_5/Setting_the_Time_Zone_for_a_Workbench_5_Job

 

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