Best way to convert to a member's timezone with offsets stored in database?

My server's timezone is in Eastern Time. When I first built my members table I went ahead and required members to set their timezone when signing up so that I could add timezone support later.

For example, a member signs up and chooses Eastern time for their timezone. The information in the database would look like "-5".

The default timestamp I have stored is in Y-m-d H:i:s format. Now how di开发者_JAVA技巧fficult would this be to convert using only the offsets? I want to submit one timestamp (whatever timezone would be best to use with offsets) into the database when a post is made and then just convert it to the user's timezone based on their offset in database when the content is displayed to them.

Any sample code or short tutorial would be great.

Thank you.

Assuming you did the right thing and are storing timestamps using the MySQL timestamp datatype, and not the datetime date type, all you have to do is SET time_zone = .... as a MySQL query, and the timestamp will convert.

datetime data types do not convert on their own, but you can convert them yourself.

You will have some issues with daylight savings time though. It's best to store timezones as America/New_York, and things like that. Not numbers.

See the php timezone_identifiers_list() function which will give you a list of possible timezones. The result of that can be used with both PHP and MySQL. I typically load the timezone from the users profile then do:

date_default_timezone_set('....');
mysql_query("SET time_zone = '....'");

Then everything just works.

Pretty easy. Php has pretty extensive time zone support.

http://www.php.net/manual/en/class.datetime.php