Let’s say that your server runs in Central Time but it logs data for facilities all across the US. 99% of the time you won’t have any problem. You’ll grab the Date/Time out of the database, and if displaying to a facility on the East Coast, you convert the Date/Time to Eastern for the user to see. It’s all good. However, you set yourself up for a very big headache in the Fall.
In the Fall, the clocks will move back, which means that your server will experience 1am – 2am twice. If MySQL stored the offset in the DateTime field (e.g. -05:00 before the change and -06:00 after the change), then this wouldn’t be a problem. However, since it doesn’t, when you pull a log out of the table and get the time 1:30am, you have no way of knowing which 1:30am it is, the one before or the one after the change.
If only you had stored the times in UTC you would know which was which.
However, we have a bigger problem. Your Easter timezone facilities’ clocks don’t change back at the same time…they change back at their own 2am. This means that when you translate back and forth between Central and Eastern time, you are going to run into some odd glitches illustrated below. The “Time In” is the valid time sent from the Eastern timezone facility, the “Time Stored” is the time that it gets converted to on the local machine before storage. The “Time Out” is the translation back to local machine time when it pulls it out of the table, and the “Display Time” is the end-result that you display in your logs for the Eastern timezone facility.
|Time In||Time Stored||Time Out||Display Time|
|01:00 EDT||00:00 CDT||00:00 CDT||01:00 EDT|
|01:30 EDT||00:30 CDT||00:30 CDT||01:30 EDT|
|01:00 EST||01:00 CDT||01:00 CDT||01:00 EST|
|01:30 EST||01:30 CDT||01:30 CDT||01:30 EST|
|02:00 EST||01:00 CST||01:00 CDT||01:00 EST|
|02:30 EST||01:30 CST||01:30 CDT||01:30 EST|
|03:00 EST||02:00 CST||02:00 CST||03:00 EST|
You’ll notice that you’re all good up until you get to 2:00am EST. Your local machine converts it to local time as 01:00 CST, which is correct. However, since MySQL doesn’t store the offset, when your local machine pulls it out of the database table, it translates it to 1:00am CDT since it doesn’t know which. This results in your Eastern timezone logs having 3 instances of 1am – 2am instead of 2, and a complete loss of the logs from 2am – 3am.
Therefore, if you are working with multiple timezones, or your business might grow to include multiple timezones…start things off the right way. You can avoid this problem by storing your DateTimes in any format that is immune to DST changes. So, the following will all work:
It can be a bit of a pain getting used to storing data in UTC and then translating it into the display timezone later. However, it avoids many pitfalls, and the work you do up-front to get used to it will pay off in the long run.
Kevin Nelson December 18th, 2014
Posted In: Software Design