Dealing with PHP server and MySQL server in different time zones

Question

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?

Answer 1

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.

Answer 2

Store everything as UTC. You can do conversions at the client level, or on the server side using client settings.

php - date

mysql - utc-timestamp