A Tale of Two Timezones

I work with date and time data a lot. I write applications that handle tens of thousands of such records a day. Not sure if I can tell you the exact nature of my work, so I’ll just briefly touch on the peripheral. Besides, if I write it here, I’ll have to hunt down the 7 people who read my blog. Excluding you of course. You’re awesome.

Big Ben
[image by track5]

So a company in United Kingdom provides a … certain service. And this service produces most of the data I work with. United Kingdom and Singapore… not quite London and Paris though…

Anyway, the data is accessed by customers all around the world, so I’ve got to align the dates and times correctly. Luckily, the customers understand the data is in UTC, so I didn’t have to explain too much in the user interface. I just have to present the date/time information correct to the second.

Internally, there’s a fork in opinions. Should the times when the backend processes are run be in UTC, or Singapore time? Purists might say, “All times should be in UTC!” and they convert accordingly. Pragmatists might say, “I’m the only who reads those logs, so it should be in local time!” The customers won’t know what went on in the backend processes, but they might be interested in when their data was last refreshed (which happens to be the process date/time).

This creates an interesting problem. You have to standardise the handling of any date/time data logic, even if it’s just to get the current date/time. Sometimes, I forget whether a particular piece of data is in UTC or local time, and I had to dig out the code that handled it to find out. Because 8 hours is a big difference.

Adding to this confusion, is that the system times of the web server and database server might be off by a few hundredths of a second. So do you use DateTime.UtcNow in C# or getutcdate() in SQL? Does it even matter? I used to work for a manager who insisted all times to be based on the database server’s system time. I admire his stand. I also have to point out the many database calls wasted just to get the current time in the applications…

On the upside, I have to wait for the data to be consolidated for the previous day, before it’s sent over to Singapore for processing. UTC midnight is 8am in Singapore, which is about the time I start work. This gives me some time to react in case of emergencies. You take whatever blessings you have, however small…