Your Timezone or My Timezone?
Your Timezone or My Timezone?
We gotta choose one. Which one are you going to choose?
Customer: "I'd like to see the daily average utilization ratio of all equipment globally. And I also need to be able to drill down to individual continents, countries, and districts."
Billy: "Sure. Which timezone should be used?"
Customer: "Good question. Our headquarters is in Eastern Standard Time. Let's use EST, then."
Billy went ahead to add the required metrics shortly.
Customer: "This is perfect. Oh, I forgot one thing. All offices should see this in their local timezones."
Billy: "Which timezones do we have to support?"
Customer: "I'm not sure, as we have quite a few locations. Can we support all the timezones? And I think people on travel also need to see this in the local timezone wherever they are."
Billy: "Okay. I think we can dynamically detect the local timezone, no problem."
Well, it turned out there were many significant problems.
Too Many Timezones
First, the world has around 40 timezones, more than just 24 that most people believe. Many countries and areas use 15, 30, and even 45-minute-off timezones. BTW, a country's timezone can change for various reasons, even adding new timezones to the world.1
Billy created separate pipelines, tables, and columns for each timezone. The more timezones, the higher the runtime cost becomes. It was not a viable solution.
Billy returned to the customer to seek to reduce the total number of timezones to be supported. In the end, he added the timezone management features. The customer can configure the set of timezones to be supported. A lot of complexity was introduced.
Inconsistent Metrics
In the customers' high-level operation meetings, executives found that the metrics on their screens occasionally differed from those on others' screens. This created quite a drama.
It turned out people's dashboards were based on their local timezones. The metrics were different here and there, which caused a lot of confusion.
The customer complained about the accuracy of the data. Soon, the system became untrustworthy.
Proper Way to Handle Timezones
To serve the purpose of effective communication, metrics must be consistent. Within a company, it is simply wrong to have personalized dashboards or reports for different time zones.
No matter where the viewer is around the world, the dashboard should always show the same values for the same metrics.
So, how do we handle timezone differences?
The answer to that is we don't. For analytics and reporting purposes, where the viewer is shouldn't matter. What matters is the timezone of the target objects. In this case, the location of the equipment.
For equipment in EST, we use EST to calculate their metrics. For equipment in PST, we use PST to calculate. The equipment utilization ratio would read "85% on Jan 20th" for daily metrics and "83% for 9:00 AM on Jan 20th" for hourly metrics. When we mix and compare equipment from both locations, we only care about "Jan 20th" for daily and "9:00 AM on Jan 20th" for hourly. For business reporting purposes, we don't compare based on the absolute same time (like comparing 9:00 AM EST to 6:00 AM PST).
Technically, the implementation is quite simple:
Store all timestamps in UTC, along with the original timezone information. Ex. "2024-01-20T09:00:00-05:00" to "2024-01-20T14:00:00+00:00" and "EST", or "2024-01-20T09:00:00-08:00" to 2024-01-20T17:00:00+00:00" and "PST".
Generate date and hour dimensions based on the original timezone but without the timezone in the output. For example, "2024-01-20" and "09:00" are used for both.
Use the dimensions to generate the dashboards and reports.
Billy no longer needs separate pipelines, tables, and columns for multiple timezones. And the customer gets a consistent dashboard.
Other Common Timezone Questions
A few other commonly asked timezone-related questions may again cause confusion.
How should we show equipment from multiple timezones together on the same screen?
Viewing the detailed, raw equipment data is more common for monitoring purposes. In this case, show the timezone information along with the timestamp. Make it easy for users to know which timezone individual equipment is in.
Alternatively, some customers ask for conversion to a single timezone on the screen, either detected from the browser's local setting or controlled by users.
Since we store all timestamps in UTC and keep the original timezone information, it is relatively easy to implement either way.
How do we deal with vehicles driving through multiple timezones?
Vehicles should still have an assigned "business" timezone; all reporting should use that as the base. The fact that they drive through multiple timezones does not make it any different. What matters is the elapsed time and the trajectory. Having a base timezone to "attach" the correctly calculated result is all we need.
Conclusion
Always question the requirements and always make sure to talk to the right stakeholders.2 In the case above, the specific customer was from the IT department and was unfamiliar with the business requirements. By unthinkingly following the wrong requirements, the system's complexity and overall cost increase. Worse yet, the system becomes untrustworthy to the actual users.
https://www.reuters.com/article/us-venezuela-time/call-me-cuckoo-its-chavez-time-in-venezuela-idUSN1927682520070919/. I honestly cannot imagine the kind of chaos such change caused.