MySQL Forums
Forum List  »  General

Re: Storing timezone with datetime
Posted by: James Birchall
Date: July 23, 2009 11:42AM

Robert, as Rick James says, there is no way to store timezones as a unique data type in MySQL. Ie// you can't store a time and include a timezone field as a function of some time datatype.

All is not lost though...

You can store a fk to the mysql.time_zone table which, when joined to the mysql.time_zone_name table, will give you what you're looking for. You'll have to make sure to load the timezone info for the database (see mysql_tzinfo_to_sql) before those tables will be populated though.

Rick James was suggesting that you build this structure yourself, which is fine and all, but why not take advantage of the data already there? Timezones are tricky things and the built in structures are pretty good (plus, you'll need them anyways to do the conversions).

If you do go that way, you should also create a stored procedure that will do the translation for you (ie// given an ANSI standard timezone name or offset, get the timezone id) so you don't have to include an extra join query each time you need to set the time + timezone. This will make your SQL statements a little more generic too at the expense of a per-record lookup.

Be aware that using the 'offset' form of timezone specification might match multiple timezone names, with potentially different time zone transition rules.

I'm not sure if the mysql.time_zone.Time_zone_id fields are "stable" (meaning, whether they are standard across all MySQL instances or if they're a function of the data at the time you run the script) or not, though I've never seen them change either. Doing it this way will complicate the maintenance of these tables in the future as you'll have to test each of your timezone upgrades to make sure that there aren't any data issues (you'd probably do that anyway though). You may have to modify the standard load scripts if you discover an issue.

If you use the TIMESTAMP datatype (and don't need to consider the dreaded Y2038 problem!) then you can get away with storing timezone + time in an 8-byte field (the same size as a DATETIME field). If the times need to persist longer, then you're stuck with 12-byte time records for any time values you need to capture.

Hope this helps.

James Birchall, I.S.P., ITCP

Options: ReplyQuote


Subject
Written By
Posted
Re: Storing timezone with datetime
July 23, 2009 11:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.