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.

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

  1. I’m excited to uncover this site. I wanted to thank you for your time due
    to this fantastic read!! I definitely loved every little bit of
    it and I have you book-marked to look at new information on your blog.

    May I simply say what a relief to discover an individual who genuinely knows what they are discussing
    on the net. You certainly understand how to bring a problem to light and make it important.
    More and more people have to look at this and understand this side of the story.
    I can’t believe you aren’t more popular given that you surely possess the gift.

    Excellent post. I definitely appreciate this site. Thanks!

    It’s hard to come by experienced people for this subject, however, you sound
    like you know what you’re talking about!
    Thanks

    You need to take part in a contest for one of the most useful websites on the internet.
    I’m going to highly recommend this site!

    A fascinating discussion is worth comment. I think that you need to write more on this subject matter, it might not be a taboo matter but generally people do not discuss these topics.
    To the next! Many thanks!!

    Hey there! I just wish to give you a big thumbs up for your excellent info you have got right here on this post.
    I am coming back to your site for more soon.

    After I originally commented I seem to have clicked on the -Notify me
    when new comments are added- checkbox and from now
    on each time a comment is added I get four emails with the same comment.
    There has to be an easy method you are able to remove me from that service?

    Kudos!

    The very next time I read a blog, I hope that it doesn’t disappoint me just as much as this one.
    After all, I know it was my choice to read, but I really
    thought you’d have something useful to talk about.

    All I hear is a bunch of crying about something that you could fix if you were not too busy
    seeking attention.

    Spot on with this write-up, I seriously think this site needs a lot more attention. I’ll probably be returning to read through more, thanks for the advice!

    You’re so interesting! I don’t think I’ve read a single thing like this before.
    So nice to discover another person with a few unique thoughts on this topic.
    Seriously.. thanks for starting this up. This web
    site is something that’s needed on the web, someone with some originality!

    I enjoy reading an article that can make people think.

    Also, thank you for allowing for me to comment!

    This is the right website for everyone who hopes to understand this
    topic. You understand so much its almost tough to argue with you
    (not that I personally would want to…HaHa).
    You certainly put a fresh spin on a subject
    that has been written about for years. Wonderful stuff, just excellent!

    Aw, this was a really good post. Taking the time and actual effort to make
    a great article… but what can I say… I put things off a whole lot and don’t seem to get anything
    done.

    I’m amazed, I must say. Seldom do I encounter a blog that’s equally educative and interesting, and
    without a doubt, you’ve hit the nail on the head.
    The problem is something not enough folks are speaking intelligently about.
    Now i’m very happy that I found this in my
    search for something regarding this.

    Oh my goodness! Impressive article dude! Many thanks, However I am encountering issues
    with your RSS. I don’t understand the reason why I cannot join it.
    Is there anybody else having identical RSS problems? Anyone that knows the answer can you kindly respond?
    Thanks!!

    An outstanding share! I’ve just forwarded this onto a friend who had been conducting a
    little research on this. And he in fact ordered me lunch due to the fact that I discovered it
    for him… lol. So let me reword this…. Thanks for the meal!!

    But yeah, thanx for spending time to talk about this issue here on your web site.

    After going over a number of the blog posts on your website,
    I seriously appreciate your way of writing a blog.
    I saved as a favorite it to my bookmark website list and will be checking back soon. Please visit my web site too and
    let me know how you feel.

    This site certainly has all the info I wanted about this
    subject and didn’t know who to ask.

    There is certainly a lot to know about this
    subject. I love all of the points you’ve made.

    You made some good points there. I looked on the internet for more
    info about the issue and found most individuals will
    go along with your views on this site.

    Nice post. I learn something new and challenging on websites I
    stumbleupon on a daily basis. It will always be interesting to read through content from other authors and practice something from other
    web sites.

    I blog quite often and I truly appreciate your information. This article
    has truly peaked my interest. I will take a note of your site and keep checking for new details about once a week.
    I opted in for your RSS feed as well.

    Pretty! This has been an extremely wonderful post. Thanks for providing this
    information.

    Greetings! Very useful advice within this post! It’s the little changes which will make the largest changes.

    Thanks a lot for sharing!

    Howdy! This article couldn’t be written much better!
    Looking through this post reminds me of my previous roommate!
    He constantly kept preaching about this. I most certainly will
    send this article to him. Pretty sure he’s going to have a
    good read. Thank you for sharing!

    Hello, I think your blog might be having internet browser
    compatibility issues. Whenever I look at your site in Safari,
    it looks fine however when opening in IE, it has some overlapping issues.
    I merely wanted to provide you with a quick heads up!
    Other than that, great site!

    Having read this I thought it was really enlightening.
    I appreciate you spending some time and effort to put this information together.
    I once again find myself personally spending way too much time
    both reading and leaving comments. But so what, it was still worthwhile!

    Howdy! I could have sworn I’ve visited this site before but after looking at a few of the posts
    I realized it’s new to me. Nonetheless, I’m definitely happy I found it and I’ll be book-marking it and checking
    back often!

    I needed to thank you for this fantastic read!!
    I absolutely enjoyed every bit of it. I have you book-marked to look at
    new things you post…

    Hi, I do believe this is an excellent website.
    I stumbledupon it 😉 I am going to revisit yet again since I book-marked it.
    Money and freedom is the greatest way to change, may you be
    rich and continue to guide others.

    Your style is very unique in comparison to other folks I have read stuff from.
    I appreciate you for posting when you have the opportunity, Guess I will just bookmark this web site.

    I used to be able to find good info from your blog posts.

    Excellent post! We will be linking to this great content on our site.
    Keep up the good writing.

    That is a good tip particularly to those new to the blogosphere.
    Short but very precise information… Thanks for sharing
    this one. A must read article!

    I couldn’t resist commenting. Exceptionally
    well written!

    bookmarked!!, I love your site!

    Very good post. I am experiencing many of these issues as well..

    Way cool! Some extremely valid points! I appreciate you writing this write-up plus the rest of the
    site is really good.

    Great site you have here.. It’s hard to find quality writing like yours nowadays.
    I seriously appreciate people like you! Take care!!

    This is a topic that’s near to my heart… Cheers!

    Where are your contact details though?

    I seriously love your blog.. Great colors & theme.
    Did you build this website yourself? Please reply back
    as I’m planning to create my very own site and would love to find out where you got this from
    or exactly what the theme is named. Many thanks!

    Everyone loves it when people get together and share thoughts.
    Great website, keep it up!

    Great info. Lucky me I recently found your site by accident (stumbleupon).
    I’ve book-marked it for later!

    This website was… how do you say it? Relevant!!
    Finally I’ve found something that helped me. Thanks a lot!

    Everything is very open with a precise explanation of the challenges.
    It was truly informative. Your website is very useful.
    Many thanks for sharing!

    I’d like to thank you for the efforts you have put in penning
    this website. I’m hoping to check out the same high-grade content from you in the future as
    well. In fact, your creative writing abilities has inspired me to get my own,
    personal website now 😉 https://twitter.com/safewellmart

  2. I truⅼy love your blog.. Ꮐreat color & theme. Did уou mɑke thіs wweb
    site үourself? Please reply baϲk ɑs I’m hopinjg to cгeate my օwn personal website
    and want to find out where yоu gⲟt thiѕ fгom or justt
    what thee theme is named. Kudos!

Leave a Reply

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