Timezone Settings - Need Assistance

ckatzman

We are a national company, serving customers from Hawaii to Maine. Our corporate headquarters is located in the Pacific Time Zone, our transaction servers record all transactions (regardless of point of origin) in Central Time Zone (timestamped), we have employees located in all four Continental US Time Zones. When I view the "hour" of a transaction, I want to see it based on the actual Central Time server timestamp, I don't want it adjusted -2 Hours, systemically adjusted for my location in Pacific Time Zone. The only way I've figured out how to do that is to leave my Workbench Job setting and the Corporate Settings (in Admin) to reflect UTC. The problem with doing that is as I sit here at 7:10pm on June 30, UTC is already "tomorrow". So any cards I have built with the intent to reflect "Yesterday" results now show ZERO/blank results. UTC is looking for 6/30 to represent "Yesterday", but that won't exist for 7 more hours until my refresh fires. I have a field in my data that tells me what my "End of Business Day" date is and I could hard code something in Beast Mode to test on that and match dates, but then my users lose functionality if they want to look at Last Week instead of Yesterday. Has anyone found a solution to this, or able to guide me on proper configuration for Time Zone between data, workbench, and Admin settings? Thanks!

ckatzman

Tyler -

 

While I have not implemented the changes yet due to some necessary coordination, here's what I have learned, and hopefully you will find this helpful.

 

If your date fields have a DATE datatype, then you shouldn't have an issue.  But if your date fields are of a DATETIME datatype, then read on...

 

If you are loading data through Workbench, there is a hyperlink for Job Configuration.  In the corresponding pop-up, you can set the timezone that represents the basis for your data (probably wherever your servers are located).  By doing so, DOMO translates your records' timestamp FROM wherever they are recorded (lets use US Central time as an example), and adds +6 Hours to convert the result TO UTC (which for our purposes, and given the clock adjustment, is effectively moving it to Greenwhich Mean Time, or the equivalent +/-0 Hours time).

 

THEN, in the Domo Admin Company Settings page, set your Company Timezone to match wherever your data came from - in my example, US Central Time.  So, what does all that do for you?  Workbench/Job Configuration adds X hours to your timestamps; Domo Admin Company Settings subtracts those same X hours.  Net zero impact, right?  But not really... now Domo knows where your data is based, and supposedly can now manage your "Yesterday" based on YOUR timezone.  What happened if you left everything at UTC is that as soon as your "yesterday" records became 24 hours older (by timestamp) than GMT, they started to evaporate from your Domo card results.

 

Again, keep in mind... this is all based on feedback and guidance I received from DomoSupport - I have not yet implemented this on my side, but the approach makes sense.  If you get a chance to try it before I do, please post back how it worked for you!

kshah008

Hi all,

 

Is anybody albe to assist @ckatzman with their timezone issue?

tylersnielsen

ckatzman, were you able to get some help with this?  I face the same issue and it makes things very difficult to use the 'yesterday' results.  

 

I'd also be interested to know how you came up with Business Days in Domo.  I tried a few approached but ended up creating business days in Excel and then loaded it to Domo.

ckatzman

Tyler -

 

While I have not implemented the changes yet due to some necessary coordination, here's what I have learned, and hopefully you will find this helpful.

 

If your date fields have a DATE datatype, then you shouldn't have an issue.  But if your date fields are of a DATETIME datatype, then read on...

 

If you are loading data through Workbench, there is a hyperlink for Job Configuration.  In the corresponding pop-up, you can set the timezone that represents the basis for your data (probably wherever your servers are located).  By doing so, DOMO translates your records' timestamp FROM wherever they are recorded (lets use US Central time as an example), and adds +6 Hours to convert the result TO UTC (which for our purposes, and given the clock adjustment, is effectively moving it to Greenwhich Mean Time, or the equivalent +/-0 Hours time).

 

THEN, in the Domo Admin Company Settings page, set your Company Timezone to match wherever your data came from - in my example, US Central Time.  So, what does all that do for you?  Workbench/Job Configuration adds X hours to your timestamps; Domo Admin Company Settings subtracts those same X hours.  Net zero impact, right?  But not really... now Domo knows where your data is based, and supposedly can now manage your "Yesterday" based on YOUR timezone.  What happened if you left everything at UTC is that as soon as your "yesterday" records became 24 hours older (by timestamp) than GMT, they started to evaporate from your Domo card results.

 

Again, keep in mind... this is all based on feedback and guidance I received from DomoSupport - I have not yet implemented this on my side, but the approach makes sense.  If you get a chance to try it before I do, please post back how it worked for you!

SeanPT

Thank you for these posts. I would love Domo to add a "just don't mess with my timestamps, ok?" button.

 

We have restaurants across the country and the POS records everything in local time. As far as I'm concerned, local time is what I want. If I want to see how busy we are between 11 and 1 I want everyone's local 11 and 1. And since all that data comes into one SQL server, it would be a nightmare to even think about tweaking what comes in.

 

I am currently re-uploading ~ 40 million records that I have now set to our corporate timezone which is what I have set in Domo so hopefully this does the trick.

 

[Deleted User]

SeanPT,

 

Feel free to propose your idea in our ideas exchange here. It will then get reviewed by product management and can be voted up by clicking on the white arrow by your fellow Dojo members.

 

Thanks in advance!

Dani

DataMaven

Hey Guys - The timezone issues are plentiful, and are consistently in the ideas exchange. 

 

If I don't already have one in there, it's on my list of things to add.  I had to eat some serious crow when I realized a Salesforce report I had scheduled to run on the first of each month, with criteria of 'prior month' ran on UTC.  This meant that it ran at like 8PM on the last day of the month, and thus pulled 2 months prior.  It was a new project showcasing how swanky Domo was, and let's just say it wasn't my favorite learning experience!  

 

Here are the results on Ideas Exchange:

https://dojo.domo.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=true&filter=location&location=idea-board:Ideas&q=time%20zone

 

And here's the search pop-up in Dojo, in general:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We all definitely need better time zone handling.  Anything scheduled should have a drop-down for time zone.  If Domo needs to convert THAT to UTC to normalize and make things happen at the right time, that's fine, but it should not be something the users need to contend with.  Likewise, times in our datasets should not be messed with, unless specified.  

 

This is definitely a hot topic that many Domo users are struggling to work around.  I'm sure it's tough to solve on the back end, or Domo would have taken care of it by now.  But perhaps that little something extra is needed to bring it to the finish line.  

DataMaven

WOW - and I just saw the dates on this!  I don't know why Dojo showed it as a recommended thread in the sidebar for me!