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.
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 SELECT ed 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.
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.
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?”
Share This Article
Sean Hudgston is well-balanced fellow who lives and works as a software engineer in Syracuse, NY. Sean has a degree in Computer Science, and when not doing things related to that, he enjoys brewing beer, making coffee, growing hops, cooking, disc golf, mowing his lawn, and playing guitar or bass as the situation may warrant.