Keith Devens .com

Storing times in MySQL

Starting principle: Because MySQL doesn't support time-zones in its time formats, you should never only store time/date data in a timezone other than GMT (UTC). If you do, you actually lose information about what time it really was.

There are a few options for storing dates and times in MySQL while respecting time zones:

You can store the GMT time and an offset from it in seconds to represent the time zone.
1. The date/time being MySQL's "datetime" type, and the timezone offset in "seconds from GMT". This takes 8 bytes for the datetime type and 3 bytes for the mediumint you'll need to be able to store all possible timezone offsets. Unfortunately, it just misses being able to be stored in a smallint (smallint range is -32768 to 32767, but time offsets from GMT can be ±43200 (there are 86400 seconds in a day)). (11 bytes total per row)
2. You can store the date/time using normal unix timestamps. This should be fine for most things, and takes 4 bytes for the int to represent the time and 3 bytes for the mediumint timezone offset. (7 bytes total per row)
You can store both the localtime and GMT time:
1. Each one being a MySQL datetime (16 bytes total per row) (WordPress does this, incidentally)
2. Each one being a unix timestamp (8 bytes total per row)

I mentioned the total size requirements not because that's a very important issue but just because it's something worth keeping track of. What's most significant in the choice is what type of burden you put on your application by what you choose.

If you need to store "normal" dates (where "normal" means "in between 1901 and 2038") I think my preferred solution is option 1B. By storing the date in GMT and storing the offset in seconds you're going to need to do some date math anyway, so it's better to just have everything in the DB be in the same "units". Your application will then have to take care of all your date logic, but that seems better than having some in your DB and some in your code and having to constantly convert between timestamps and MySQL's datetime format.

However, I'm honestly not sure if that's feasible. For instance, I use some of MySQL's date math in my code, and it's not immediately obvious to me how I'd replace it. For instance, if you want to get rows grouped by the day, how would you do this in MySQL if you were using unix timestamps? Ultimately, what WordPress goes for might be the simplest and best, though it feels like duplication of data to me.

Update (Jul 28, '04): See this update for my conclusions and recommendations.

Permalink | 8 Comments | Tags: Programming, This website | id 5011

14:32 (utc-4)
edited: 2004-07-28 20:00 (utc-4)

Comments

Well, here's one reason. I'd like to know what time things were done in my localtime. Given daylight savings time, if I only store GMT, I won't really know what time, localtime, things were done unless I also check to see if DST was in effect in code.

Another example is that if I move to a different time zone I'd like to have my new blog times change to my new localtime but leave the old ones the same.

One of the things that's kind of freaking me out is this: my permalinks are based on my localtime posting. The official date for this post is July 15 and that's the date in the permalink. If I was in Sri Lanka (or wherever), my post might have been on July 16th. What if it's May 1 here and I travel to a different time zone and post something on April 30 (their) localtime? It makes me want to organize my weblog only according to GMT to avoid stuff like that, but then it won't reflect what time I'm actually living in.

Being able to treat MySQL dates as dates and storing them in datetime format like WordPress does is looking more and more attractive.

Thanks Matt. I'm most likely going to follow your lead here. I probably wouldn't have thought of doing it that way if I hadn't seen WordPress do it first. Incidentally, when I was writing this post I looked back on your reply to the e-mail I sent you asking about this. Thanks for all the insight.

And, portability is much more of a concern for you with WP because it's software that other people use. My software's just for me so I have the luxury of not worrying about hassles like portability if I don't want to.

Thursday, July 15, 2004

Storing times in MySQL

Comments

Recent comments