Who’s Time is it Anyway?
So… if you find two times laying around in a database, say 9pm and 6am, how would you (and the amazing CEP software that you are writing) calculate the number of hours between 9pm and 6am the next day?
Sounds simple?
Well, as we all know assumptions are the mother of all fuckups.
The first assumption that is normally made is that these two times are times in our current time zone.
The next is that these are times both from the same time zone.
These times are not usable is you don’t know for sure what time zone they refer to. Your 9pm does not happen at the same time as mine.
Normally, if you come this far, the next assumption seems to be that is enough to have an offset from CET for each of these times. So you would assume that 9pm CET-10 and 6am CET+4 would give you the correct answer.
Wrong again.
You need to consider the evil of daylight saving time. If some countries and regions time is shifted forward and backward twice a year. And the beauty of it is that the rules for when this is done is specific to regions and coutries. So countries lying in timezone CET+1 could have different rules for when daylight saving is done.
Another assumption is that the days on which daylight saving is done never changes. Wrong too. They don’t change too often. But the do change. So basically you need to look this up from some reference data which need to be updated every day in order to stay on top of things.
So if you have come this far you might get it right.
First, you need to know in what time zones each of these times are assumed to be. You need the specific version of the time zone. For example if the first time is a departure time from Cocos Islands you need to know that they might be at GMT+6:30. Yes, another complication. Not all time zones are at an even hour boundary. If the other time is an arrival time to Australia and their Central Western Time, they might be at GMT+8:45.
So by now you might have realized that a time like 9pm is not usable if you don’t know in what location it is assumed to be in and what day it is.
You need the exact date also when calculating the difference between 9pm and 6am the next day as it might be that night daylight saving kicks into effect, and this would mean that you either skip on hour or add one magic hour.
Basically this is not rocket science. But there’s lots of small things to consider and most of the times developers will get this wrong.
Try testing your CEP software. Specify a time period which spans over a night when the daylight saving adjustment is done and see if it does the right thing.
If your DB is to be queried from across a time zone — not necessarily over web — you shalt have a TimeStampUTC column for each record.
And all timediff() calculus shall be done using the TimeStampUTC column.
A popular strategy is indeed to store everything in UTC and consider time zones purely as a presentation and data input issue. I think this approach works in most situations and gives a simple system. Here the system would always run in UTC no matter what time zones the users are in. The system would obviously require that all times are sent into it in UTC (or with a timezone) and all output is in UTC. It would be the responsibility of the user of a timestamp to convert it to what ever time zone she is in.