Working with Dates and Times in PHP and MySQL

When working in any programming language, dealing with dates and time is often a trivial and simple task. That is, until time zones have to be supported. Fortunately, PHP has one of the most potent set of date/time tools that help you deal with all sorts of time-related issues: Unix timestamps, formatting dates for human consumption, displaying times with time zones, the difference between now and the second Tuesday of next month, etc. In this article I’ll introduce you to the basics of PHP’s time functions (time(), mktime(), and date()) and their object-oriented counterparts, and then take a look at MySQL dates and show you how to make them play nicely with PHP.

PHP Date and Time Functions

Much of this article will work with Unix time, or POSIX or epoch time as it is otherwise known. Time is represented as an offset in the amount of seconds that have ticked away since midnight of January 1, 1970, UTC. If you’re interested in a complete history of Unix time, check out the Unix time article on Wikipedia.

UTC, also know by its full name Coordinated Universal Time, also referred to as GMT, and sometimes Zulu time, is the time at 0-degrees longitude.  All other time zones in the world are expressed as a positive or negative offsets from this time. Treating time in UTC and Unix time will make your life easier when you need to deal with time zones. I’ll talk more on this later, but let’s ignore time zone issues for now and look at some time functions.

Getting the Current Unix Time

time() takes no arguments and returns the number of seconds since the Unix epoch. To illustrate this, I will use the PHP interactive CLI shell.

sean@beerhaus:~$ php -a
php > print time();
1324402770

If you need an array representation of the Unix time, use the getdate() function. It takes an optional Unix timestamp argument, but defaults to the value of time() if one isn’t provided.

php > $unixTime = time();
php > print_r(getdate($unixTime));
Array
(
   [seconds] => 48
   [minutes] => 54
   [hours] => 12
   [mday] => 20
   [wday] => 2
   [mon] => 12
   [year] => 2011
   [yday] => 353
   [weekday] => Tuesday
   [month] => December
   [0] => 1324403688
)

Formatting a Unix Time

Unix time can be easily formatted into just about any string that a human would want to read. date() is used to format Unix timestamps into a human readable string, and takes a formatting argument and an optional time argument. If the optional timestamp is not provided, the value of time() is used.

php > print date("r", $unixTime);
Tue, 20 Dec 2011 12:54:48 -0500

The “r” formatting string returns the time formatted as specified by RFC 2822. Of course, you can use other specifiers to define your own custom formats.

php > print date("m/d/y h:i:s a", $unixTime);
12/20/11 12:54:48 pm
php > print date("m/d/y h:i:s a");
12/20/11 01:12:11 pm
php > print date("jS of F Y", $unixTime);
20th of December 2011

For the entire list of acceptable formatting characters, see the page for date() in the PHP documentation. The function becomes more useful though when combined with the mktime() and strtotime() functions, as you’ll see in the coming examples.

Creating Unix Time from a Given Time

mktime() is used to create a Unix timestamp given a list of values that correspond to each part of a date (seconds, minutes, hours, year, etc). It takes a number of integer arguments to set each part of the date in this order:

mktime(hour, minute, second, month, day, year, isDST)

You set isDST to 1 if daylight savings is in effect, 0 if it’s not, and -1 if it’s unknown (the default value).

php > print date("r", mktime(12, 0, 0, 1, 20, 1987));
Tue, 20 Jan 1987 12:00:00 -0500
php > print date("r", mktime(0, 0, 0, date("n"), date("j"), date("Y")));
Tue, 20 Dec 2011 00:00:00 -0500
php > print date("r", mktime(23, 59, 59, date("n"), date("j"), date("Y")));
Tue, 20 Dec 2011 23:59:59 -0500

You can see that mktime() can be very helpful when dealing with database queries that use date ranges customized by a user. For example, if you’re storing timestamps as integers (Unix time) in MySQL (foreshadowing anyone?), it’s very easy to set up a common year-to-date query range.

<?php
$startTime = mktime(0, 0, 0, 1, 1, date("y"));
$endTime   = mktime(0, 0, 0, date("m"), date("d"), date("y"));

Parsing an English Date to Unix Time

The almost magical function strtotime() takes a string of date/time formats as its first argument, and a Unix timestamp to use as the basis for the conversion. See the documentation for acceptable date formats.

php > print strtotime("now");
1324407707
php > print date("r", strtotime("now"));
Tue, 20 Dec 2011 14:01:51 -0500
php > print strtotime("+1 week");
1325012569
php > print date("r", strtotime("+1 week"));
Tue, 27 Dec 2011 14:03:03 -0500
php > print date("r", strtotime("next month"));
Fri, 20 Jan 2012 14:04:20 -0500
php > print date("r", strtotime("next month", mktime(0, 0, 0)));
Fri, 20 Jan 2012 00:00:00 -0500
php > print date("r", strtotime("next month", mktime(0, 0, 0, 1, 31)));
Thu, 03 Mar 2011 00:00:00 -0500

PHP’s DateTime and DateTimeZone Objects

PHP’s DateTime object is the object-oriented approach to dealing with dates and time zones. The constructor method accepts a string representation of a time, very similar to strtotime() above, and some might find this more pleasant to work with. The default value if no argument is provided is “now”.

php > $dt = new DateTime("now"); 
php > print $dt->format("r");
Tue, 20 Dec 2011 16:28:32 -0500
php > $dt = new DateTime("December 31 1999 12:12:12 EST");
php > print $dt->format("r");
Fri, 31 Dec 1999 12:12:12 -0500

DateTime’s format() method works just like the date() function above, and accepts all of the same formatting characters. DateTime objects also come with a few useful constants that can be fed to the format() method.

php > print $dt->format(DATE_ATOM);
2011-12-20T15:57:45-05:00
php > print $dt->format(DATE_ISO8601);
2011-12-20T15:57:45-0500
php > print $dt->format(DATE_RFC822);
Tue, 20 Dec 11 15:57:45 -0500
php > print $dt->format(DATE_RSS);
Tue, 20 Dec 2011 15:57:45 -0500

The complete list of constants can be found on the DateTime documentation page.

Since we will soon be dealing with time zones, let’s give PHP a default time zone to use. In your php.ini configuration file (I have one for CLI and one for Apache) find the section that looks like this:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
; date.timezone =

When no value is given to date.timezone, PHP will try its best to determine the system time zone as set on your server.  You can check which value PHP is using with date_default_timezone_get().

php > print date_default_timezone_get();
America/New_York

Let’s set the time zone of the server to UTC time (date.timezone = UTC) and save the configuration file. You will have to restart Apache or the CLI shell to see the changes.

PHP DateTime objects include an internal DateTimeZone class instance to track time zones.  When you create a new instance of DateTime, the internal DateTimeZone should be set to your default provided in php.ini.

php > $dt = new DateTime();
php > print $dt->getTimeZone()->getName();
UTC

The complete list of acceptable time zone names can be found on the time zone documentation page.

You can now see the difference in times when two DateTime objects are given different time zones. For example, here’s a sample that converts from UTC to America/New_York (EST) time.

php > $dt = new DateTime();
php > print $dt->format("r");
Tue, 20 Dec 2011 20:57:45 +0000
php > $tz = new DateTimeZone("America/New_York");
php > $dt->setTimezone($tz);
php > print $dt->gt;format("r");
Tue, 20 Dec 2011 15:57:45 -0500

Notice the -0500 offset for the month of December.  If you change the the time value to a summer date, such as July 1, you’ll see it is aware of Daylight Savings Time (EDT).

php > $tz = new DateTimeZone("America/New_York");
php > $july = new DateTime("7/1/2011");
php > $july->setTimezone($tz);
php > print $july->>format("r");
Thu, 30 Jun 2011 20:00:00 -0400

Using dates with MySQL and PHP

If you have used MySQL at any level, you’ve probably noticed the DATETIME type that is provided out of the box. It looks and smells like a date, and if you said it was a date then you would be right.  But once you have SELECTed it out of MySQL and into PHP, all you really have is a string that looks like a date. It has no time zone awareness and is already formatted for human consumption before a human ever needs to look at it.*

* Yes I know MySQL has plenty of date formatting functions, but we’re already dealing with PHP too which, as you have seen, kicks ass at dealing with date formats. Why would we want it formatted right out of the database anyway? We may have a few different transformations and formats we want to apply to it. It’s best to format your date only when a human is about to see it.

<?php
$db = new PDO("mysql:host=localhost;dbname=testdb", "dbuser", "dbpassword");

$result = $db->query("SELECT dt_date FROM some_table");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
    var_dump($row["dt_date"]);
}
$result->closeCursor();

Running this simple script, you can see that all you have from the DATETIME field is a formatted string with no time zone information.

Array
(
   [dt_date] => 2012-01-16 13:03:49
)
string(19) "2012-01-16 13:03:49"

The DATETIME value is the local time of the server running MySQL in whatever time zone that is. If all you are ever doing only concerns one server in one time zone, DATETIME will probably fit most of your needs and I am highly jealous of you.

So how do you deal with dates and time zones with PHP and MySQL?  Store dates as Unix timestamps.

You already know that Unix time is the number of seconds since January 1, 1970 in UTC, so that gives you a constant time zone to work with, and hopefully you’ve noticed that many of PHP’s date/time functions are based on a Unix timestamp.

When working with MySQL, I usually create table columns that will store dates as INTEGER UNSIGNED. When inserting a date you can then use either time() from PHP or UNIX_TIMESTAMP() from MySQL.

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1326738918 |
+------------------+

If you want MySQL to format the date, you can.  But the time will be in the server’s time zone and I suggest you hold off on any type of formatting until you are at the template/view level in your web app and are ready for human eyes to see it.

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
+---------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP()) |
+---------------------------------+
| 2012-01-16 13:37:16             |
+---------------------------------+

In any time zone spanning application, you will typically have a table that keeps track of users’ custom time zone settings which will then be read into a $_SESSION value. Assuming you have a session entry that looks like this:

$_SESSION["userTZ"] => "America/Chicago"

You can easily convert a stored Unix time (in UTC) to any date in a particular user’s time zone.

<?php
$result = $db->query("SELECT int_date FROM some_table");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $dt = new DateTime();
    $tz = new DateTimeZone($_SESSION["userTZ"]); 
    $dt->setTimestamp($row["int_date"]);
    $dt->setTimezone($tz); 
    print $dt->format("r");
}

This would result in the date “Mon, 16 Jan 2012 12:03:49 -0600″. The -0600 tells you it is 6 hours behind UTC, which has an offset of 0.

If we were to change the time zone setting to America/Los_Angeles, the resulting date would be:

Mon, 16 Jan 2012 10:03:49 -0800

And America/New_York would yield:

Mon, 16 Jan 2012 13:03:49 -0500

Summary

Dealing with dates and time zones are an every day part of many programmers’ lives, but it’s nothing to worry about when you have PHP’s robust and easy-to-use date libraries to work with.

You’ve seen how easy it is to get a Unix timestamp, how to format a date into any imaginable format, how to parse an English representation of a date in to a timestamp, how to add a period of time to a timestamp, and how to convert between time zones. If there are two main points to take away from the article, they would be to 1) stick with Unix time and 2) stick with UTC as the base time zone for all dates when working with PHP and MySQL.

The idea of basing all time on UTC is not one that only applies to PHP and MySQL; it’s considered good practice in any language. And if you ever find yourself working in another language, there is a good chance you will say to yourself “Dammit, why can’t they do it like PHP?”

Image via Yakobchuk Vasyl / Shutterstock

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Dave

    When using dates, setting the timezone is essential if you have a strict PHP setup for error reporting (E_STRICT). For example, when installing PyroCMS recently, I had to set the default timezone in fizl.php in order to proceed with the install.

  • http://www.anthonyw.net Anthony

    I would like to point out that if you want to work with times before epoch you have to use SIGNED integers in your database schema.

    • http://www.SilkAndSlug.com Steve Almond

      Seconding Anthony’s comment. Wasted some time yesterday tracking a bug that turned out to be a date of birth pre-1970. Unix-times can be negative, people!

  • Michel Merlin

    Thanks for this interesting and useful page. May I suggest a few improvements to it (from my own experience: in 1992 I wrote an entire time system for our Lab’s large Finite Elements Analysis application, to replace the original UNIX components with a more coherent, more reliable, more precise system, tested on all UNIX variants and underlying hardware: SunOS to Solaris, AIX, HP-UX, etc, stations and servers from Sun, HP, IBM, SGI, DEC, etc. For instance this took in account the 2 transitions, Gregorian and Zero Year. This is why that system has worked sans problem since; e.g. when fears started about the Y2K bug, I publicly said we would NOT be affected, and on the Y2K night I made sure all our UNIX stations and my PCs were running and connected, including at home, to prove it).

    Most of what I have to say is detailed in “Mandatory in Written-Date-and-Time: 3EN Month; TOG; DOW” of Fri 11/11/11 11:11:11 GMT ( http://goo.gl/lIv85 ) and its main sub-link “PST and PDT; TZ and TOG; UTC and GMT; Internet Date & Time; PHP date” of Tue 16 Jan 2007 14:04:05 GMT ( http://goo.gl/ZJnzI ). I recall hereafter the most important points:

    1) Your page could IMO recall that 99% of the email messages on earth have the Date & Time formatted compliant with RFC 2822 (now RFC 5322) in their MIME source. This is proof that this format is well understood and accepted by programs and people all over the planet, resolves any ambiguity between nations and continents, so it is better to ALWAYS and EVERYWHERE write the Date & Time in that RFC 5322 format, which (as you appropriately recall) is referred as “r” in PHP documentation.

    2) DOW (Day Of Week) and Month must be written in 3-letter English abbreviations. Months in digits are the biggest cause of errors in dates. Entire names too often get shortened, other languages are too often miswritten or misunderstood.

    3) TZ (Time Zones) must NOT be used. Replace them with TOG (Time Offset over GMT), as mandatory by RFC 5322. See links above for details.

    4) As soon as on a recorded (i.e. persistent) media (written, or taped), “Date & Time” must be seen as ONE SINGLE ENTITY, always written COMPLETE. Be particularly careful at never omitting DOW or TOG; see “Mandatory…” link for explanation. Such handling as a single and complete entity removes all the problems addressed in the “Using dates with MySQL and PHP”paragraph.

    5) The “Date & Time” entity must be stored in UNIX time, i.e. in (real) number of seconds elapsed (positive or negative) since 01 Jan 1970 00:00:00 GMT, thus alleviating all TZ or TOG problems. Conversions to/from user-friendly format (local time or other TZ, regional formats, etc) must be done as close as possible before/after user interaction. For instance, Microsoft would never have had a single time problem if it had decided to store the Date & Time in Windows in UNIX time (numbers of seconds, GMT), and converted to user format just before display and eventually converted back immediately after changed by user or program.

    6) Relative Date & Time (“Yesterday”,”3 hours ago”) are fine in verbal dialog; they are inappropriate, imprecise or ambiguous, misleading, as soon as on a recorded medium. Always write Date & Time in absolute format.
    Versailles, Tue 13 Mar 2012 22:00:00 +0100

  • Alex

    Does anyone know if a future version of PHP will support Unix time past 01/19/2038? Is this something that is planned for PHP6?

    • Michel Merlin

      Y2038 “problem” as dummy as Y2K one
      ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
      “Alex” Tue 13 Mar 2012 21:07 GMT ( http://phpmaster.com/working-with-dates-and-times#li-comment-3873 ), UNIX time is a number of seconds. The problem you recall is just another Y2K: a “problem” raised by people who spend more time yelling than thinking. As I publicly said as soon as 1997, Y2K “bug” will never strike reasonable programmers, who, while silent, are the majority.

      In my case I handled dates over centuries back to before Christ and to after Year 3000; so when thin brains started to cry about Y2K, I knew year 2000 was just a particular case of a particular case totally addressed in much more general way in my software – as probably in most serious software’s around the world.

      With Y2038 it’s the same: the “bug” assumes programmers are dumb enough to store Date & Time (a number of seconds in UNIX time) as a SIMPLE INTEGER, i.e. in a 32-bit integer including the +/- sign, hence limited to 2^31 seconds, i.e. 2,147,483,648 seconds, or 596,523.235,6 hours, or 24,855.134,81 days, or 68.049,650,42 years, before or after UNIX epoch, i.e. limited to (approx) years 1902-2037.

      I was already sure, and I am even more after Y2K, that most programmers are reasonable enough to store the Date & Time entity in a large enough container; in my case since 1992 I put it in a DOUBLE PRECISION REAL, which gives more precision than needed in all ordinary human times and eras, and degrades progressively and nicely when you stray away from those eras.

      Versailles, Wed 14 Mar 2012 11:30:00 +0100

      • Alex

        Michel,
        Yes clearly there are work-arounds. I could just store the unix time as a string in a BLOB and then use PHP’s intval() function.
        My question was meant to ask if there is a REAL solution for this issue planned in an upcoming version of PHP.
        It is not a “Dummy” question when you have to employ a hack to ensure that it works for dates in the not-so-distant future.

        • Alex

          Following up on my previous reply: Personally I have always used YYYY-MM-DD HH:MM:SS (with the timezone either stored in a separate field or set by the application depending on what I needed) for my timestamps to avoid any possible issues of pre-1970 or post-2038 dates. This works great for most applications because it is still easy to order, it can be converted to other formats easily enough, and if you only have a date without a time, it can still be ordered along with the complete time stamps.
          That being said, there are a lot of nice features when using the unix time stamp, and I would like to use it in some situations if I didn’t have to worry about it failing on post-2038 dates.
          Clearly Michel has more experience and expertise than I do when it comes to working with dates, but I don’t think he understood what I was asking.
          I would be curious to know what other developers use.
          Thanks and have a great day everyone!