administration mode
Pssst...Ferdy is the creator of JungleDragon, an awesome wildlife community. Visit JungleDragon

 

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 the Convert_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.

Share |

Comments: 6
Reviews: 2
Average rating: rating
Highest rating: 5
Lowest rating: 4

COMMENT: TANNY O'HALEY emailhomepage

APR 19, 2010 - 10:54:42 PM

comment » The problem is finding an automatic method for a "reliable way to detect the current user's timezone". I've run into this problem with Domino web applications. While Domino has a manual methodology to set the time zone, that relies on the end user to set their time zone.

You can calculate the time zone using javascript and pass it to the server, but some time zones overlap. At least the time will display correctly.

I wonder if the safest way is to send the browser date/time in UTC and convert it on the browser side. «

COMMENT: BAS PETERS rating

APR 21, 2010 - 06:04:10 PM

comment » These issues are extremely familiar, because I have dealt with them extensively during the design and implementation of a huge PHP/MySQL platform.

First, I would like to mention that one should always store date/times in UTC, not GMT, reason being that GMT is subjected to DST while UTC isn't. Also, unix epoch integers are always relative to UTC.

Second remark is the complexity of calculating the DST for a given user. There are tons of variables to be considered. A good example is Australia. They have three time zones, but only 3 of the 6 states observe DST. As a result, when DST is in effect, there are actually 5 different times in Australia, +8, +9.5, +10, +10.5 and +11. The exact start and end date and time when DST is observed also differs per country and is subject to change every year.

In order for MySQL to be timezone aware, MySQL needs to know information about geographical locations and DST settings for each region. A lot of MySQL distributions on various platforms do not supply nor update this information and it is up to the developer or sysadmin to populate the correct records in the mysql.time_zone* tables. There is even a chance of a mismatch between the host OS timezone handling and MySQL that needs special attention to be avoided. As you have mentioned in your article, Timestamps are not timezone relative, but Date/Time fields are. The simplest approach is to set both the database server and the application server to UTC time to avoid mistakes. A simple insert in a client tool like phpMyAdmin can cause unwanted timezone conversions when not paying attention.

Luckily on most Linux distributions, the timezone information is automatically updated each year through the base repositories and as long as you keep the system updated, this should go smooth. On windows however, it is a whole different story. There are a lot of known timezone related problems with windows and MySQL. On windows it is necessary to manually update timezone related information in MySQL.

As far as native PHP support for timezones, any developer will soon notice that there is a pretty huge gap of functionality between PHP 5.3+ and earlier PHP versions. Crucial functions like calculating a difference over two timestamps, converting a timestamp to an epoch and constructing a timezone aware date/time from a string format are all 5.3+. The build-in DateInterval class proves to be very useful when calculating timezone aware date/times but regrettably it is 5.3+ only.

Many developers unable to host their project on PHP 5.3 resort to sub optimal techniques, such as subtracting integer epoch timestamps and use modulo to calculate the offset in months, days, minutes and seconds. This might work for cosmetic dates that are not required to be 100% accurate, but for important business systems it makes a world of difference. Unix epochs are relative to UTC, but come with a large BUT. Calculations based on seconds do not respect subtle corrections in time such as leap seconds. The correction of this issue is very complex, as it involves calculating the TIA time with an extensive table of corrections. As a result, one can say that epoch times are approximate at best. Also there is a range limitation of 1970 to 2038 and dates outside this range cannot be used.

There is a nice trick to get UTC times from MySQL and convert them into a local user timezone. One of the properties of a MySQL client connection is the client timezone. This setting can be changed before querying and MySQL will automatically convert all date/time fields to the client timezone.

A last remark that comes to mind, be wary of the date_default_timezone_set() function in performance sensitive environments. Tests showed a significant increase in processing time when used in a bootstrap include. It is better to set this in the PHP.INI, and as of PHP 5.3 it is even mandatory to avoid runtime errors when using the DateTime object. «

COMMENT: FERDY

APR 23, 2010 - 09:37:39

comment » Bas, your comment far surpassed the depth and completeness of this mini article. Thanks so much! «

COMMENT: JASON STEVENS emailhomepagerating

MAY 10, 2010 - 07:39:33 AM

comment » I am still trying to get my head around timestamps. What I need to be able to do is to give the user a set of time stamps to select from, which then sets their timezone, or offset. I understand that I have to store the timeformat in neutral time. Actually what I am trying to do is have a list of events. Each event is in a different timezone. When data is entered in, i was thinking it should be entered in as "event, timezone offset"

because event x could be in canada, event y in the UK, but the user needs to select the location of the event, and set the time of the event in that location.

hope that makes sense? «

COMMENT: MAX

JUL 24, 2011 - 02:54:13 PM

comment » thanks it's very useful «

COMMENT: ALEJANDRO

DEC 9, 2013 - 01.36.51

comment » Thanks, just one correction... time() isn't timezone dependent, because it returns the number of seconds elapsed since the Unix Epoch, which is a UTC date. What IS timezone dependent is date() as you propbably know as you used gmdate(). «

RATE THIS CONTENT (OPTIONAL)
Was this document useful to you?
 
rating Awesome
rating Good
rating Average
rating Poor
rating Useless
CREATE A NEW COMMENT
required field
required field HTML is not allowed. Hyperlinks will automatically be converted.