I take time for granted, especially time zones. When asked if a certain time works for a meeting, I assume that the time zone is the same as the one I am in. That logic began to fail when I began working remote with people all over the United States and the world.
I am equally careless about time zones when building applications.
I took over support for a report that had never given the results the owner expected. The counts were always off. Different stores would record the activities for a particular day only to see those activities missing on the report. They would record 10 activities and would only see 5.
I looked at various hypotheses.
- Delay in uploading data to report
- Delay in recording activity in the application
- Data uploaded too before the activities were entered
None of these explained what I was seeing.
Then one store provided me a separate record of their activities. The manager had recorded these activities in a spreadsheet at the same time that they entered the activity in the application. The times were all very close to three hours different from what we had in our database. And more importantly, the manager had entered those missing activities after 10 PM PST (Pacific Standard Time, USA). The values in our database were recorded in EST (Eastern Standard Time, USA), three hours ahead of PST. The times were different, and more importantly for the report. the days recorded were different.
When handling dates, you need to be very specific about the time zone used. The time zones recorded in the database could have various sources:
- Local time zone (I’m currently in the Eastern Daylight Time zone)
- Computer time zone (my computer is in the Central Daylight Time zone)
- My work time zone (currently Pacific Daylight Time zone)
- The application server time zone
- The database server time zone
- Universal time
Here are some thoughts about how to be specific about time zones in your application and database design.
One quick note. If I had been involved with this project from the beginning, I would have made exactly the same mistakes that the original report writers made. Any insights I gained were from hindsight, not foresight.
Add time zone to requirements
In the report, we needed to be explicit about the time zone used to place activities into day buckets. The requirement that was never made explicit was:
The day of the activity is the day that the activity was recorded in the store’s time zone
The original report writers were working with an implicit requirement:
The day of the activity is the day that the activity was recorded by the application
Using the explicit requirement would have raised questions about the dates that we could have solved earlier.
- What system sets the time zone in the data warehouse? Does the application get the time from the client or server?
- What time zone is that system in?
Document time zone
It is important to document for everyone the time zone choices. User documentation is especially essential when the choice of time zone is counter intuitive. Documenting the time zone for the report would have included the following entry.
The day of the activity is the day that the activity was recorded by the application, which is in Eastern Standard Time
My guess is that if the store managers were apprised of this definition , they would have pushed back and we would have had some very profitable discussions.
This documentation is essential for another reason. It highlights a risk when you are considering moving the application server. If you are using the time zone of the application server and the application server moves to another time zone, this will help you to see the impact of such a move. If you move the application server to a new time zone, your reports will suddenly and inexplicably begin to misplace some of the activities.
Ways to fix
There are different ways that we can fix the problem.
Change the application
This is the most challenging, in fact near impossible unless the application is a custom application. Some of the suggestions below can be used.
One that is specific to an application is that the application can get the time zone information from the client. This means requiring the client application to include time zone information when recording the acitivity.
Use user profile to hold time zone
Many applications use user profiles to store information. You can do the same. You tie each record to a profile that includes time zone information.
In my report each row is associated with a store. I would include in my store table a time zone value. In the report, I would then convert the time from the application time to the store time using that table, adding or subtracting a certain number of hours from the time recorded to get the time in the store’s time zone.
I would go further and include a profile entry for the application that includes the time zone. Then rather than hardcoding the application to store time differential, you can determine that differential by comparing the store time to application time. If you hard code the conversion to the store time zone assuming the application is always in the same time zone, you will have problems when moving the application server to a new time zone. You will need to find every occasion where the adjustment logic is used in order to fix the conversion code. Rather if you store the time zone in a configuration, changing the configuration will automatically adjust the conversion logic.
Final note
In looking back at how I have handled dates in the past, I realize that I have ignored time zone almost all the time. I have not been bitten because most of the time I have been working with companies located in one time zone or that has defined a company standard time zone.
In this increasingly distributed business world, we don’t have that luxury.
Please include in the comments other examples where being careless with time zones has impacted your applications. Also, provide other ways to handle time zones.