Msg#: 4198376 posted 8:37 am on Sep 8, 2010 (gmt 0)
I currently have all my code processing time zones as GMT however one of the last things I have gotten around to is storing the time zone preference the user chooses. So my question is what it the optimal data type for storing time zones? In example I see in an old copy of phpBB they store time zones in a column set to decimal(5,2), plus I'm not sure what kind of values to expect in there inherently...perhaps if you're on a half-hour split zone something like 4.3 (for +4:30) or -3.3 (for -3:30)? I just want to be sure I'm going about this the correct way.
Msg#: 4198376 posted 11:30 am on Sep 8, 2010 (gmt 0)
I wouldn't store timezones in values, because you can't cope with daylight savings time that way. Better is to store the timezone as VARCHAR() and use the ASCII timezone identifiers. Most scripting languages have functions to convert UTC times to local times when you supply the timezone identifier.
If you are using PHP, a list of accepted timezones can be found here [php.net].