Date and Time in PHP 5 and MySQL 5

Time for a little technical post.

Date and time manipulation are one of the most common components in web applications. This is true for a few reasons:

  1. Web applications can have users coming from anywhere around the globe, under many different timezones.
  2. Chances are that you receive date and time information when the user does something. For instance, a user sends an e-mail and there is a timestamp in the message header.
  3. Chances are that you want to do something about the timestamp. For instance, sorting e-mails according to the timestamp before showing them to the user.

Unfortunately, the PHP date and time manipulation functions are anything but properly documented by the PHP documentation team. This causes a lot of grief and frustration among novice PHP programmers. Here, I will talk about how to handle dates and times in a PHP 5 + MySQL 5 setup.

Always store dates and times in the UTC timezone.

This is a good idea for 3 reasons:

  1. The UTC time reference is the most precise and least debatable there is on Earth because it is based on atomic time and takes the Earth’s rotation speed into account for compensation. Furthermore, it has never been, is not, and will never be subject to the daylight saving rule.
  2. The UTC is already the preferred reference timezone among application developers. The Unix Epoch, for instance, starts on midnight of January 1st, 1970, UTC time. Therefore, storing dates and times in UTC will allow easier interoperability with other applications right off the bat.
    1. A consequence of the above is that robust conversion routines to and from UTC is likely to be available in any programming language you pick (including PHP).
  3. You never know when you may need to move or deploy your web application to a host on a different timezone, therefore you don’t want to rely on the server’s timezone for storing and converting dates and times.

Dealing with date and time in PHP 5

Let’s face it, the php.net documentation sucks. One of the issues I see in it is that it fails to adapt to the OOP paradigm. It’s a shame that OOP has been introduced to PHP 4 and 5, and yet the documentation is still largely function-oriented. PHP 5 comes with a class library, but most people whom I have talked with do not know how to use it because the classes are not well documented. As such, most PHP 5 projects are still largely procedural. My goal here is to bring you a little bit of enlightenment by explaining how to use those classes.

  1. First, you need to create an object to represent the timezone. This step is not strictly necessary, but is recommended because in a globally connected world, a time value by itself is meaningless. The DateTime constructor can optionally take it as an argument, which is the way I recommend you to use it.
    $timezonename = 'America/Montreal'; 
    $mytimezone = new DateTimeZone($timezonename);

    Note that $timezonename can be any timezone name supported in the Olson timezone database (a.k.a. zoneinfo) or any of the few extra ones. You can see the full list in Appendix I of the php.net documentation.

  2. Only then do you create the DateTime object.
    $datetimestring = '2007-03-13 23:10:00';
    $mydatetime =
      new DateTime('2007-03-13 23:10:00', $mytimezone);

    The exact acceptable format of $datetimestring is the same as the GNU Date Input Formats, in case you have not found out from the php.net documentation already.

  3. Now, let’s try outputting it as a string. The bad news is that you should not do echo $mydatetime; The good news is that you can decide what string representation to take by using the DateTime::format() method.
    $mydatetimeformat = 'Y-m-d H:i:s e'; 
    /**
     * 'Y' for year, 'm' for month, 'd' for date,
     * 'H' for hours, 'i' for minutes, 's' for seconds,
     * and 'e' for the timezone name
     */
    echo $mydatetime->format($mydatetimeformat), 
      $mydatetime->format('I')?' DST':'';
  4. Perhaps you would like to see the date and time in another timezone? No problem, PHP has that covered.
    $mytimezone2 = new DateTimeZone('Asia/Tokyo'); 
    $mydatetime->setTimezone($mytimezone2); 
    echo $mydatetime->format($mydatetimeformat), 
      $mydatetime->format('I')?' DST':''; 

Word of caution

The constructor method of DateTime is supposed to return false on failure, so you may be under the impression that you can rely on it to validate date and time strings. For instance:

$montrealtimezone = new DateTimeZone('America/Montreal'); 
$bogustime =
  new DateTime('2007-03-11 02:30:00', $montrealtimezone);

The above snippet is supposed to result in $bogustime holding the value false because ’02:30:00′ is not a valid time on that date due to Daylight Saving Time entering into effect. Unfortunately, the constructor does not catch the mistake and will return you a DateTime object representing March 11th, 2007, two-thirty in the morning, Montreal Daylight Saving Time. It is a bug that I recently discovered in PHP 5.2.0 and submitted to the php bug database. It does not seem to be fixed as of this writing, where the latest stable released version of PHP is 5.2.1. You can check the status of the bug here: http://bugs.php.net/?id=40340.

Edit:
A comment by Eric has prompted me to clarify that the above bug is NOT caused by an outdated zoneinfo database. You can test it by trying to instantiate a similarly bogus date of a past year, while having the most up-to-date zoneinfo database installed.

6 thoughts on “Date and Time in PHP 5 and MySQL 5”

  1. Cool. You updatd your site. I haven’t been to your site for so long because it was inactive…. Yea, for that bug in Word of Caution, it could be caused by the Daylight Saving Time change change. Yes, time change change. It used to be at a different date. Now they changed it and everybody needs to install stupid patches to their servers.

  2. The new DST start and end dates are just part of the story. If the constructor of DateTime were properly designed and implemented, then a simple upgrade of the zoneinfo database would have solved the problem. Unfortunately, that’s not the case, as can be verified if you try to instantiate a similarly bogus date of a past year, while having the most up-to-date zoneinfo database installed. For instance, ‘2006-04-02 02:05:00 America/New_York’ would still be accepted as valid by the constructor, even though it really shouldn’t be, using the latest version 2007d zoneinfo database.

    I suppose I should have clarified that it is not caused by an outdated zoneinfo database.

  3. how should i do so that when someone make an update into the system, it’s will capture the time and date the changes are make. For e.g SwordAngel post a comment on ‘ March 21st, 2007 at 11:34 pm’. How i can stored this information into my database.

  4. Tasha,

    In MySQL, you can add a column of type TIMESTAMP to any table. The column can be configured such that
    1. it is initialised to a certain time value (current time by default) when you insert a new record;
    2. it is updated to a certain time value (current time by default) whenever you make changes to a record;
    3. both of the above; this is the default behaviour if you do not specify the parameters when creating the column.

    Read more at http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

  5. Thank you for this quick tutorial. I was very disappointed with the PHP Manual on these built in classes. This tut was a life saver. Now if only I could get my client to understand why it took me 5 hours to get timezone conversions worked into the sites user system, I’ll be set.

  6. Thanks for your analysis and comments. I think PHP is, overall, a good language. But they screwed up BIG-TIME (and screwed US up) when they took it upon themselves to do automatic DST conversions on our dates, with no way to disable this. We do not use DST and have it turned off on our server, but PHP assumes it is on regardless. So my America/Chicago tz works only half the year. MySQL always works properly — I give it a unix timestamp and it gives me the correct date and time that go with that timestamp. It does not try to subtract or add hours based on a timezone.
    Responsible and adept programmers have always handled timezone issues programmatically. We don’t need a language doing it for us, ESPECIALLY when we’ve got legacy data going back over 10 years! Shame on the PHP team for doing something like this.

Leave a Reply

Your email address will not be published. Required fields are marked *