Dealing with PHP server and MySQL server in different time zones

For those of us who use standard shared hosting packages, such as GoDaddy or Network Solutions, how do you handle datetime conversions when your hosting server (PHP) and MySQL server are in different time zones?

Also, does anybody have some best practice advice for determining what time zone a visitor to your site is in and manipulating a datetime variable appropriately?

This question and answers originated from www.stackoverflow.com
Question by (8/20/2008 5:55:35 PM)

Answer

As of PHP 5.1.0 you can use date_default_timezone_set() function to set the default timezone used by all date/time functions in a script.

For MySql (quoted from MySQL Server Time Zone Support page)

Before MySQL 4.1.3, the server operates only in the system time zone set at startup. Beginning with MySQL 4.1.3, the server maintains several time zone settings, some of which can be modified at runtime.

Of interest to you is per-connection setting of the time zones, which you would use at the beginning of your scripts

SET timezone = 'Europe/London';

As for detecting the client timezone setting, you could use a bit of JavaScript to get and save that information to a cookie, and use it on subsequent page reads, to calculate the proper timezone.

//Returns the offset (time difference) between Greenwich Mean Time (GMT) 
//and local time of Date object, in minutes.
var offset = new Date().getTimezoneOffset(); 
document.cookie = 'timezoneOffset=' + escape(offset);

Or you could offer users the chioce to set their time zones themselves.

Find More Answers
Related Topics  php  mysql  datetime  time  dates
Related Questions