Time zone (bug)havior summary and questions

DataSquirrel

I just stumbled across some issues in how Domo manages datetime values and have some questions. I've looked through the docs and the Dojo and see that my questions aren't rare. After reviewing a lot of materials, I'm still not clear about how to show charts in local time when you have users or facilities in different time zones. I could really use some guidance here. Help is much appreciated.

Question Summary

I tend to go on, so I figured that I should put my questions up top for anyone kind enough to answer them. Some are explained in more detail later.

• How do you design cards to show data based on the current user's local time zone when you have users in multiple time zones?
  
  
• For a multi-time zone system, is there any reason to change the corporate time? (All of our data is UTC to start with.)
  
  
• Does anyone know how this works when pulling data from Postgres? I haven't tried it yet. All timestampz data in Postgres is UTC. All the time. But you can display/convert it, if needed. Is this needed or does Domo accept the data as UTC? (I can test this on my own if no one knows off the top of their heads.)

Working Assumptions

First, I'd like to make sure that I've got the basics right. Additions and corrections greatly appreciated.

• Domo wants and expects datetime values to be in UTC. So bad.
  
  
• If you import values direction into the Data Center (or onto a KPI card), the data is _assumed_ to be in UTC and is not changed.
  
  
• If you import (exactly the same) values through Workbench, the data is _assumed_ to be in the machine's time zone and is automatically shifted to UTC.
  
  
• You can set a "corporate time zone", but only one. I have left our server at UTC and haven't messed with this setting. I think that what it does is magically make searches/graphs work in "corporate time" while leaving the raw data in UTC.
  
  
• The individual time zone setting is just for alerts, etc. and has nothing to do with the rest of this.
• Datetimes don't offer any particularly good way to handle datetime comparisons based on "clock time." For example, if you want to check on a mid-day rush, your PST time is going to be at 4AM (or so) because California is UTC-8. So, noon in Dublin is 4AM in San Francisco.

Note that we currently have control over out key data formats and haven't loaded so much data yet that we can't reformat and reload our key files. Okay, now I'll hit a few things in more detail.

Workbench Bug(s)

I find that I'm not the first to notice that Workbench seems to automatically convert datetimes on the assumption that the values are expressed in the machine's local time. This happens before you can do anything with the data. It's a weird behavior, but it's kind of documented once you know to look. And the preview pane reveals the behavior, if you notice it. So, I guess it's a "bughavior" because it's buggy, but documented and there is a workaround. You add a Shift Data Timezone Transform back to the local time zone to re-offset the UTC distortion from Workbench's automatic transformation. It makes my head hurt to think about, honestly.

 

I guess that I can kind of understand this assumption that datetimes are in local time when the datetime isn't clear about the timezone. (Although it has to be the case that feeds are coming in from different time zones, which makes the Workbench machine's time zone a bit of a random guess.) In our case, all of our data is already in UTC and I use an ISO 8601 (I always have to look the number up) format that says so:

 

2017-12-25T01:00:00Z

 

I've tried an alternative standard format with the same results:

 

2017-12-25T01:00:00+00:00

 

These datetimes are explicitly in UTC, so the way Workbench automatically shifts them is a bug. 

 

The screenshots below show the same data imported via Data Center (top - correct) and Workbench (bottom - wrong):

I also noticed that the datetimes come through with different formats, for what that's worth. The extra column is a Beast Mode on each DataSet to extract the day name. This worked, showing that Domo sees the values as datetimes properly.

 

It's pretty buggy feeling that the same data pulled in via Workbench and Data Center come through differently.

Clock/Local/Wall Time Versus Universal Time

Sometimes we want to compare facilities across time zones based on "business days" or times of day, like "morning rush" or "Sunday night." Doing that with a universal time line (UTC) is hard. In fact, welcome to a world of pain. I thought this one through and realize that it's not really up to Domo. I think that we need three fields in those cases:

 

I assume that you can use a Beast Mode to figure out local date and time but, in our case, we should be able to add fields for these cases.

Multiple "Local" Modes

Okay, now here's the problem that I don't know how to solve. Our main server is in UTC-6, I'm in UTC+10, and we've got clients all over the lower 48. If we have customers in San Francisco, Denver, Chicago, and Miami, how do we show each of them charts in their local time. We can pick a corporate time, say Chicago, but that doesn't help out with any other time zone. What do people do in this case? Lots of custom charts and best modes?

 

What would be handy would be a user/group setting that works like Corporate Time Zone, but for the current user or their group (?) I'm just starting to look at users and groups in Domo, so I might be confused here. In any case, I put in a suggestion for this here:

Support user/group specific time zones, like 'corporate' time zones

 

Thanks very much for any help! 
 

DataSquirrel

I neglected to mention that I expect some of our datetime conundrums can be solved by adding additional datetime columns to our DataSets. For example, datetime_utc and datetime_pst, for different purposes. In most cases, we control the data formats and can add columns in advance without having to add an ETL transformation or Beast Mode. Still, this only makes it more important that Domo Workbench not molest our values.

Byboth

I need some help with this.  Please see my scenario below

 

1. All date data is assumed to be in UTC in Domo

2. Our company setting is UTC

3. We have multiple time zones and want our users to see UTC data in there local time zone

 

Here is what I tried

1. Card with date time shows UTC date time in domo

2. Change the user setting to CST ( my local time)

3. View card with date date  date time shows UTC date in domo

 

I expected in step three to show in CST but still shows in UTC.  Am i assuming wrong how this works.  Need some help with this

 

jaeW_at_Onyx

https://knowledge.domo.com/Administer/Specifying_Company_Settings/033Time_Zone_Issues_FAQ

 

please review this document!  it sounds like Domo is behaving as designed.  

if it's not, can you pls provide a screenshot of the raw datatime column and a screenshot of what you're seeing in cards?  Domo should adjust the data to the correct timezone in analyzer if configured properly.

DataSquirrel

I got pinged on this as I started the question about three years ago. Not re-reading everything, but what we've done is to add to versions of the same timestamp in different columns. Think of them as event_local_timestamp and event_utc_timestamp. Sometimes, you want local clock time, like "how busy is each location in the morning?" For that, you need local clock time, UTC makes zero sense. Then again, sometimes you want to track things in reality-time, for which you need a universal timeline, UTC.

It's 100% correct of Domo to store times in UTC, anything else is madness. But, no one thinks in UTC, we think in clock time. It's appealing to try and calculate off of UTC, but gets horrible and messy every time. When you have a UTC time, and want to convert it to local, what has to happen?

 

* You need an integrated time zone database. Postgres, and many other systems, use the Olson tz database https://en.wikipedia.org/wiki/Tz_database#:~:text=The%20tz%20database%20is%20also,was%20designed%20by%20Paul%20Eggert.) I don't know what Domo uses.

 

* Each timestamp needs to be converted. As in, each row.

 

* This is crazy hard to do, it's not algorithmic...it's rule-based. Why? Because time zones are a political construct, they change, and they don't make perfect sense. Don't get me started on DST.

 

* Fun fact! Max UTC offset? 26 hours. Eh? And while UTC between Boston and Sydney never changes, the actual time difference can vary by 0, 1, or 2 hours over the course of the year. Why? DST & the equator.

The simplest thing is to calculate the timestamps in local and UTC *before you send them to Domo*. Give each version a clear, consistent name, and then base your visualizations and summaries on the version that makes sense for the question you're trying to answer.