Dealing with Time Zones in PHP/MySQL

04 Mar


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.

$gmtTime = gmdate("Y-m-d H:i:s", time());

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).

 FROM `audittrail`
 WHERE date( CONVERT_TZ( datetime, '+0:00', '-7:00' ) ) = '2013-03-04 '

Leave a comment

Posted by on March 4, 2013 in MySQL, PHP, timezone



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: