What is the best way to handle timezone on PHP & MySQL?

Question

I am currently studying the best way to handle timezones on my website. People from many different countries will access it simultaneously, and I have to show them time-based information, so I thought:

Store every time on database according to my server (same timezone, defined by PHP) Then, the user has the option to choose his timezone, and I do the needed conversions by using mysql function DATEADD.

This seems to work fine, but my questions are:

• Is this the best way?
• Is DATEADD the most efficient function to handle the hour difference?

Thanks.

Answer 1

As described in MySQL Server Time Zone Support:

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

Therefore, if you use TIMESTAMP type columns, MySQL will handle timezone conversion for you automatically: just set the appropriate timezone for the session in its time_zone variable.

Answer 2

You are thinking in the right direction.

I would not use the server's timezone. Instead, use Coordinated Universal Time (UTC) time. It is the World Time Standard. This is pretty much the same as Greenwich Mean Time (GMT). Note that UTC does not change with Daylight Savings Time.

TO use in PHP see: http://php.net/manual/en/function.gmdate.php

From here, you can either add hours via: http://www.php.net/manual/en/datetime.add.php

Or set the timezone based on the users preference: http://www.php.net/manual/en/datetime.settimezone.php

The one you use is based on how you get the user's timezone. If you ask them for it (most accurate) you can set the timezone in PHP with the user selecting from a combo box. If you get it from the header with JavaScript using getTimezoneOffset(); then it is best to add hours based on the timezone offset.