I have recently been thinking about how to add time zone support into My Book It to allow it to expand beyond the local area. You see when My Book It started dealing with a time zone was easy. There was one. This was easy to deal with, as long as the time on the server was set correctly everything worked great.
I first ran into the issue of Time zone support when I moved the first customer from the previous hosting provider into the new SAAS version. The time zone on the server was set to something other then Australia/Brisbane. My server was set to Australia/Brisbane which gave me a different time offset from UTC. After some troubleshooting I found that I needed to go through the database and add a certain number of seconds to all of the dates stored in Unix Time String values.
While in the past that has been fine the issue is now that I need to be able to deal with customers outside of the Australia/Brisbane time zone which will cause some grief come the 6th of October 2015. (The start of Day light savings time)
Why the trouble?
So lets first look at why I am going to run into trouble. You see with an application that deals with timetables time is king. A lot of the features are dependant not only on the time being correct, but also the time being relevant to the area in which the application is used. Lets use an example.
If you have a class that starts at 8:30 on a Wednesday for example, there are a lot of things that need to happen around that time for everything to go smoothly. Some of these items include sending a reminder email, preventing changes to the class near or on the start time, the self check-in portal displaying the correct information. All of these things and more depend on the time being correct relative to the user who is using the app.
What causes the trouble?
So now that we know that time is important, lets look at what is the cause of the troubles. Time in the database is stored in a number of different methods. I can store a start time in a format similar to 08:00:00 or 14:30:00 which is easy enough to deal with as that doesn’t change depending upon the time zone selected. Also if I store a date in a fashion like 22-06-2015 that is also easy to deal with because it doesn’t change when changing time zones. (the 22nd is always the 22nd). The issue comes along when you start to deal with dates stored in Unix Time String.
Why the trouble with Unix Time String.
So Unix Time string is great if I wanted to cross time zones. (i.e. I set-up and appointment in Brisbane for 10:00am and I need someone in New York to have the same time relative to each other (for example a phone call where both parties need to participate at the same time relative to each other). For this, Unix time string is AWESOME. But the issue that I will run into is when the time changes within the same time zone everything will move either an hour forward or backwards depending on the change.
Lets give another example.
Lets say we have a class that occurs each Monday at 10:00am. When this is stored in the database the date of the class would be stored as a unix time string to allow for classes to be easily sorted. (it is heaps easier to compare a int rather then a date, stored as possibly a string).
So I would store Monday the 22nd June 2015 as 1434895200 (Which is 22nd June 2015 00:00:00 GMT + 10). This unix time string actually relates to a UTC time of Jun 21 2015 14:00:00 (Which is 10 hours earlier).
So in the same timezone after day light savings has kicked in that unix time string would actually relate to Jun 22 2015 01:00:00 (See more time zone information here http://www.epochconverter.com/epoch/timezones.php?epoch=1434895200)
See the problem. Nothing has changed except the time zone and the unix time string value is now saying 1:00am on Jun 22nd which would mean that classes will start to report as a different time (i.e. a 9:00am class before DST would become a 10:00am class after DST, which is not the case, the class is still at 9:00am relative to the person using the site)
Now for places like NSW and VIC, this isn’t going to be a huge issue. Most of the problems would occur between 11:00 pm and 2:00 am. (I have not yet seen people running classes at these times), the issue is going to come up with places that can cross dates when converting the time. (for example South Australia)
In the page provide above SA goes from +9.5 hours to +10.5 hours for DST. This in the example above would place classes from Monday back into Sunday due to the conversions.
What can be done?
This is the question that I have been dealing with over the past little while. How to best handle this and still record information efficiently and reliably to handle different time zones. From my research I have found that I could do the following options.
Store additional information for dates. – This is one that I am not really to keen on doing. If I need to store the date in both unix time string and standard date format it makes life very difficult as I now have two places to store and update the information.
Change the way I store dates. – This is a HUGE job. The entire site is based on the idea that unix time string is used to store dates. Not that this would be impossible, this would require significant rework in the code and the way that dates are filtered and sorted. (not be mention the job of converting the dates)
Store offset with the date. – This one seems like the most sane option. When storing the date in the database, I would need to add an extra column which would store the offset in which the item was created in seconds (for example GMT + 10 would be +36000). Then, when working with the dates I would need to work out the difference between the recorded offset and the current offset and then apply that to the working date.
I will cover this in more details over the next few posts as I work through the issue.