Xing \ Creative \ Coding

Web Application Software Development

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:

  • Store your Date/Time in UTC. This is the “best-practice” standard.
  • Store the offset as part of the DateTime (e.g. 2014-11-02T02:00:00-05:00). SQL Server has a DateTimeOffset column type, but for MySQL you would have to use a VARCHAR column. Using VARCHAR increases your storage space requirements while also reducing the inherent functionality provided with the DateTime column, so I don’t recommend it.
  • Store in a Unix Timestamp format. This is functionally equivalent to storing in UTC, and is great if you’re storing the time for “right now”, but it is not good for something that needs to store past or future Date/Times because it’s only good for 1970 – 2038. Also, when doing manual queries into the database just to look at the data, timestamps are not human-readable, so I never use them for anything myself.

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.

December 18th, 2014

Posted In: Software Design

Tags: , , ,

Leave a Comment