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.
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.
First, I'd like to make sure that I've got the basics right. Additions and corrections greatly appreciated.
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.
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:
I've tried an alternative standard format with the same results:
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.
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.
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:
Thanks very much for any help!
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
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.
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%20b....) 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.