Avoiding timezone nightmares in PHP and MySQL »

FERDY CHRISTANT - APR 19, 2010 (07:08:48 PM)

I am a developer that finds things like date handling, character encodings and such to be a pain. Like many developers, I pretend the problem does not exist and just hope that things turn out well. This is an attitude that I can no longer afford in this global village, which is why I'm getting to the bottom of things like this. An example is one of my earlier articles, where I had to take a deep dive into Unicode:

Building Unicode LAMP applications

Although time zones and daylight savings time are entirely unrelated, they are similar, undesirable problems that you cannot hide from. In this little article I will tell you how I deal with timezones and DST in a PHP/MySQL context. The key of this article is in avoiding timezone issues in your date handling.

Introduction

There are three aspects to date/time handling where timezones can be relevant:

• Storing date/times
• Calculating with date/times
• Displaying date/times

Storing date times

The key to storing date times is to explicitly set the timezone or to set it to a neutral timezone. You should never just take any date and insert it into a database without explicitly converting it to a specific timezone. In most cases, you are best of storing it in a neutral format, meaning the GMT timezone. The GMT timezone is also used by the UTC standard. This little PHP snippet converts the current date/time into GMT:

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

Next, one can insert this into MySQL without change if your table column is of type "timestamp". Note that in the example above, time() will give back the time using the timezone configured at the host. In my case it is on GMT+2. This means that if you actually look in the MySQL table, all dates will be 2 hours off. This is correct behavior, since we are storing the timestamp in the GMT timezone. The great thing here is that it does not matter in which timezone your host is.

(note: It is recommended though to set both your web host and MySQL to the same timezone explicitly)

Calculating with date times

Once we have stored timestamps in a neutral timezone, doing calculations on them is fairly easy. The thing to remember here is that if you are doing timestamp comparisons, your input timestamp first has to be converted to GMT too. This is best illustrated with an example:

SELECT COUNT(id) FROM c WHERE date_created > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR)

The line of SQL above returns the amount of comments that were made in the last hour. The date_created column contains the timezone neutral timestamp. Since we are calculating a date offset, our input parameter (the current timestamp) first has to be converted to GMT/UTC. That's all we need to do, because date offsets are not time zone dependent in any other way. What happens one hour ago in one part of the world happened one hour ago in any other part of the world.

Displaying date times

When it comes to displaying date/times that are sensitive to a user's timezone, it takes a conversion from the neutral timestamp to a timestamp that is optimized to the user's timezone. You can do this straight from MySQL using theConvert_TZ function.

That's only part of the story though. You will need a reliable way to detect the current user's timezone or let them set it theirselves. Next, you need a way to convert the user-friendly list of timezones to the one used in MySQL or PHP. Finally, you can then display a date/time for the user's timezone.

Luckily, in some projects you may be able to escape this mess. If your customer accepts a date format like this:

"3 days ago"

Instead of

"April 16, 2010"

...then the whole problem of displaying timezone specific dates goes away. In that case, you can use the technique used in this article.

Closing

This mini article was just me thinking out loud and structuring my thoughts around this topic. Still, I hope it helps someone else. Please do rate and comment below