RSS

Category Archives: MySQL

Dealing with Time Zones in PHP/MySQL

timezone

timezone

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

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

Advertisements
 
Leave a comment

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

 

Tags: