Working with Dates and Times in PHP and MySQL

Handling dates and times in any programming language is often a simple and trivial task until the time zone needs to be supported. Fortunately, PHP has a powerful set of date/time tools that can help you deal with various time-related issues: Unix timestamps, format dates for human reading, display dates with time zones, calculate the time difference between now and the second Tuesday of next month, and more. This article will introduce the basics of PHP's time functions (time(), mktime(), and date()) and their object-oriented counterparts, then learn about MySQL dates and show you how to make them work perfectly with PHP.

Main gains

• Efficiently handle Unix timestamps and formatted dates with PHP's powerful date and time functions such as time(), mktime() and date().
• Use PHP's DateTime and DateTimeZone objects for object-oriented date and time operations, including handling different time zones.
• Storing dates in MySQL as Unix timestamps to maintain consistency between different time zones and converting and formatting using PHP's date/time function.
• Set the server's default time zone to UTC in PHP configuration to simplify date and time management and ensure application consistency.

PHP date and time function

Most of this article will use Unix time, also known as POSIX time or epoch time. Time is expressed as the number of seconds elapsed since midnight UTC on January 1, 1970. If you are interested in the full history of Unix time, check out the Unix time article on Wikipedia. UTC (Coordinated Universal Time), also known as GMT, is the time of the 0-degree longitude line. All other time zones in the world are represented as positive or negative offsets relative to this time. Using UTC and Unix time to process time will make your life easier when you need to process time zones. I'll go into this in detail later, but for now let's ignore the time zone issue and look at some time functions.

Get the current Unix time

time() does not accept any parameters and returns the number of seconds since the Unix epoch. To illustrate this, I will use the PHP interactive CLI shell.

<code class="language-bash">sean@beerhaus:~$ php -a
php > print time();
1324402770</code>

If you need an array representation of Unix time, use the getdate() function. It accepts an optional Unix timestamp parameter, but defaults to a value of time() if not provided.

<code class="language-php">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
)</code>

Format Unix time

Unix time can be easily formatted to any string that humans wish to read. date() is used to format Unix timestamps into human-readable strings and accept a format parameter and an optional time parameter. If no optional timestamp is provided, the value of time() is used.

<code class="language-bash">sean@beerhaus:~$ php -a
php > print time();
1324402770</code>

"r" format string returns the formatting time specified by RFC 2822. Of course, you can use other specifiers to define your own custom format.

<code class="language-php">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
)</code>

See the page of date() in the PHP documentation for a complete list of acceptable formatted characters. However, when used in conjunction with the mktime() and strtotime() functions, the function becomes more useful, as you will see in the following examples.

Create Unix time based on a given time

mktime() is used to create Unix timestamps based on a list of values ​​(seconds, minutes, hours, years, etc.) corresponding to each part of the date. It accepts many integer parameters, setting each part of the date in the following order:

<code class="language-php">php > print date("r", $unixTime);
Tue, 20 Dec 2011 12:54:48 -0500</code>

Set isDST to 1 if daylight saving time is enabled; set to 0 if not enabled; and set to -1 if unknown (default).

<code class="language-php">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</code>

You can see that mktime() is very useful when handling database queries using user-defined date ranges. For example, if you store timestamps as integers (Unix time) in MySQL (foreshadows anyone?), it's easy to set a common year-to-date query range.

<code>mktime(hour, minute, second, month, day, year, isDST)</code>

Resolve English dates to Unix time

Almost magical function strtotime() takes a string in date/time format as its first argument, and a Unix timestamp used as the basis for conversion. Please refer to the documentation for acceptable date formats.

<code class="language-php">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</code>

PHP's DateTime and DateTimeZone objects

PHP's DateTime object is an object-oriented method that handles dates and time zones. The constructor accepts a string representation of time, very similar to the above strtotime(), which some people may find easier to use. If no parameters are provided, the default value is "now".

<code class="language-php"><?php $startTime = mktime(0, 0, 0, 1, 1, date("y"));
$endTime   = mktime(0, 0, 0, date("m"), date("d"), date("y"));</code>

The

DateTime method of format() works the same way as the date() function above and accepts all the same formatted characters. The DateTime object also comes with some useful constants that can be fed to the format() method.

<code class="language-php">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</code>

The complete list of constants can be found on the DateTime document page. Since we'll be dealing with the time zone soon, let's provide a default time zone for PHP. In your php.ini configuration file (I have one for CLI and one for Apache), find the section as shown below:

<code class="language-php">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</code>

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

<code class="language-bash">sean@beerhaus:~$ php -a
php > print time();
1324402770</code>

Let's set the server's time zone to UTC time (date.timezone = UTC) and save the configuration file. You must restart Apache or CLI shell to view the changes. The PHP DateTime object includes an internal DateTimeZone class instance to track the time zone. When you create a new instance of DateTime, the internal DateTimeZone should be set to the default value provided in php.ini.

<code class="language-php">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
)</code>

The complete list of acceptable time zone names can be found on the time zone document page. You can now see the time difference when two DateTime objects are given different time zones. For example, here is an example of converting UTC to America/New_York (EST) time.

<code class="language-php">php > print date("r", $unixTime);
Tue, 20 Dec 2011 12:54:48 -0500</code>

Please note the -0500 offset in December. If you change the time value to a summer date, like July 1, you will find that it knows daylight saving time (EDT).

<code class="language-php">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</code>

Use dates with MySQL and PHP

If you have used MySQL at any level, you may have noticed the DATETIME type that works out of the box. It looks and smells like a date, and if you say it is a date, you are right. However, once you SELECT it from MySQL into PHP, all you actually have is a string that looks like a date. It has no time zone awareness and is formatted for human use before humans need to view it. Yes, I know MySQL has a lot of date formatting functions, but we are already using PHP, and as you can see, PHP is excellent in handling date formats. Why do we also need to format it directly from the database? We may need to apply some different conversions and formats. It is best to format the date only when humans are about to see it.

<code>mktime(hour, minute, second, month, day, year, isDST)</code>

Run this simple script and you can see that all you get from the DATETIME field is a formatted string with no time zone information.

<code class="language-php">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</code>

DATETIME value is the local time of the server running MySQL, regardless of the time zone of that server. If all you do involves only one server in one time zone, then DATETIME may be suitable for most of your needs, and I am very envious of you. So, how do I use PHP and MySQL to handle dates and time zones? Store dates as Unix timestamps. You already know that Unix time is the number of seconds since UTC on January 1, 1970, so this gives you a constant time zone, and you may have noticed that many date/time functions of PHP are based on Unix timestamps. When using MySQL, I usually create a table column that stores dates as INTEGER UNSIGNED. When inserting a date, you can use PHP's time() or MySQL's UNIX_TIMESTAMP().

<code class="language-php"><?php $startTime = mktime(0, 0, 0, 1, 1, date("y"));
$endTime   = mktime(0, 0, 0, date("m"), date("d"), date("y"));</code>

If you want MySQL to format dates, you can do this. But the time will be in the time zone of the server running MySQL, and I recommend that you do not do any type of formatting until you reach the template/view level of your web application and be ready to see it.

<code class="language-bash">sean@beerhaus:~$ php -a
php > print time();
1324402770</code>

In any application that spans time zones, you usually have a table that tracks the user's custom time zone settings and then reads it into the $_SESSION value. Suppose you have a session entry as shown below:

<code class="language-php">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
)</code>

You can easily convert stored Unix times (in UTC) to any date in the time zone of a specific user.

<code class="language-php">php > print date("r", $unixTime);
Tue, 20 Dec 2011 12:54:48 -0500</code>

This will result in the date "Mon, 16 Jan 2012 12:03:49 -0600". -0600 tells you that it is 6 hours behind UTC and the offset of UTC is 0. If we set the time zone to America/Los_Angeles, the generated date will be:

<code class="language-php">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</code>

And America/New_York will generate:

<code>mktime(hour, minute, second, month, day, year, isDST)</code>

Summary

Handling dates and time zones is part of the daily life of many programmers, but there is no need to worry when you can use PHP's powerful and easy-to-use date library. You've learned how to get Unix timestamps, how to format dates in any imaginable format, how to parse the English representation of dates into timestamps, how to add a period of time to timestamps, and how to convert between time zones. If there are two main points in this article, it is 1) sticking to Unix time, and 2) sticking to UTC as the base time zone for all dates when using PHP and MySQL. The idea of ​​all time based on UTC is not only applicable to PHP and MySQL; it is considered a best practice in any language. If you find yourself working in another language, there is a good chance you will say to yourself, “Damn, why can’t they do the same as PHP?”

Pictures from Yakobchuk Vasyl / Shutterstock

(The FAQ section should be added here about the FAQ section for handling dates and times in PHP, similar to the FAQ section in the input text. I did not add it here due to space limitations.)

The above is the detailed content of Working with Dates and Times in PHP and MySQL. For more information, please follow other related articles on the PHP Chinese website!