Dealing with Time Zones in PHP/MySQL

[caption id="attachment_768" align="alignnone" width="502"] timezone[/caption]

You might have experienced numerous problems when dealing with timezones. It is difficult to handle timezones when your web server and users are in different timezones. The condition become worse if the database server is in another timezone.

Let's see a simple way of storing date,time in database without messing up timezone related things. The datetime can be stored in GMT format without explicitly using a  specific timezone.

[sourcecode language="php"]
$gmtTime = gmdate("Y-m-d H:i:s", time());
[/sourcecode]

the timestamp value returned by time() function is stored in GMT format. This is neutral and can be stored in database without thinking timezone problems.

When querying data and make any date time comparisons you can convert it into a desired timezone using mysql built-in function CONVERT_TZ().

Following query can be used to retrive records added on a particular today with the consideration of a specific timezone.
This is based on 'America/Denver' timezone  ( -7:00).

[sourcecode language="php"]
SELECT *
FROM `audittrail`
WHERE date( CONVERT_TZ( datetime, '+0:00', '-7:00' ) ) = '2013-03-04 '

[/sourcecode]