Time zone (bug)havior summary and questions
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.
- 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.)
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.
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.
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:
Thanks very much for any help!