How To Save GMT Date Time To Database And Showing It Back In Local Time Using PHP

Web application with date and time sensitive information are tricky sometimes when you have users coming from different timezones. The easiest solution I’ve found is to save whatever date and time into database (oracle, sql server, mysql, whatever) as GMT and convert it back to the user’s timezone when fetching it from the database to display to the user. Using PHP and MySQL, here’s how you do it.

Let’s say a user from asia with timezone GMT+8 has selected a datetime 2012-04-23 00:00:00 GMT+0800

To get the gmt datetime, we use the php function gmdate

$gmtdate = gmdate("2012-04-23 00:00:00", strtotime("2012-04-23 00:00:00 GMT+0800");

And you can use something like this to insert the date into a datetime column (let’s call it startdate)

mysql_query("INSERT INTO tableName(startdate) VALUES ('$gmtdate');

To fetch the startdate back and display it to a user who’s living in Sydney, Australia for example
*I assume you know how to query and fetch your gmt datetime stored in the database back as $gmtdate

date_default_timezone_set('Australia/Sydney');
$usertime = date("Y-m-d H:i:s", strtotime($gmtdate . " GMT"));

Notice the extra string GMT after $gmdate. That’s crucial and where most people get confused about why their GMT datetime stored in the database is not converted to a specific timezone even though they are sure it should have worked because they have used date_default_timezone_set

For example, if you leave out the GMT string, this won’t get you back the user datetime, even if you’ve defined the user’s timezone. And guess what, $usertime will just return you with what’s stored in ur database (in this case, $gmtdate)

date_default_timezone_set('Australia/Sydney');
$usertime = date("Y-m-d H:i:s", strtotime($gmtdate));

Hope this helped. Comments welcomed.