在PHP和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具有最强大的日期/时间工具集之一，可帮助您处理各种与时间相关的问题：Unix时间戳，格式化供人类使用的日期，显示时区的时间，现在与第二个之间的时差下个月的星期二等。在本文中，我将向您介绍PHP时间函数( time() ， mktime()和date() )及其面向对象的对象的基础知识，然后看一下MySQL。约会，并向您展示如何使它们与PHP完美配合。

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.

本文的大部分内容将以Unix时间或POSIX或纪元时间使用，否则它就是众所周知的。 时间表示为自UTC 1970年1月1日午夜以来滴答秒数的偏移量。 如果您对Unix时间的完整历史感兴趣，请查看Wikipedia上的Unix时间文章 。

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.

UTC，又名全称协调世界时，也称为GMT，有时也称为Zulu时间，是经度0度的时间。 世界上所有其他时区都表示为与此时间的正或负偏移。 当您需要处理时区时，以UTC和Unix时间处理时间将使您的生活更轻松。 稍后我将详细讨论，但让我们暂时忽略时区问题，并介绍一些时间函数。

获取当前的Unix时间 (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.

time()带任何参数，并返回自Unix时代以来的秒数。 为了说明这一点，我将使用PHP 交互式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.

如果需要Unix时间的数组表示形式，请使用getdate()函数。 它带有一个可选的Unix timestamp参数，但如果未提供，则默认为time()的值。

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
)

格式化Unix时间 (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.

Unix时间可以很容易地格式化为人类想要读取的任何字符串。 date()用于将Unix时间戳格式化为人类可读的字符串，并带有格式化参数和可选的时间参数。 如果未提供可选的时间戳记，则使用time()的值。

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.

“ r”格式字符串返回RFC 2822指定的格式时间。当然，您可以使用其他说明符来定义自己的自定义格式。

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.

有关可接受的格式字符的完整列表，请参见PHP文档中的date()页面 。 尽管与mktime()和strtotime()函数结合使用时，该函数变得更加有用，如下面的示例所示。

从给定时间创建Unix时间 (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()用于创建Unix时间戳，该时间戳给出了与日期的每个部分(秒，分钟，小时，年等)相对应的值列表。 它需要多个整数参数来按此顺序设置日期的每个部分：

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).

如果夏令时有效，则将isDST设置为1；如果不设置isDST ，则设置为0；如果未知，则将-1设置为-1(默认值)。

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.

您可以看到，在处理使用用户自定义日期范围的数据库查询时， mktime()可能非常有用。 例如，如果要在MySQL中存储时间戳(整数时间)(Unix时间)(是否要提前有人吗？)，则设置通用的年初至今查询范围非常容易。

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

将英语日期解析为Unix时间 (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.

几乎不可思议的函数strtotime()将日期/时间格式的字符串作为其第一个参数，并将Unix时间戳用作转换的基础。 请参阅文档以获取可接受的日期格式 。

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的DateTime和DateTimeZone对象 (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的DateTime对象是处理日期和时区的面向对象方法。 构造函数方法接受时间的字符串表示形式，与上面的strtotime()非常相似，有些人可能会觉得使用起来更令人愉快。 如果未提供任何参数，则默认值为“ 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.

DateTime的format()方法的工作原理与上面的date()函数相同，并且接受所有相同的格式字符。 DateTime对象还带有一些有用的常量，可以将它们馈送到format()方法。

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.

常量的完整列表可以在DateTime文档页面上找到。

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:

由于我们即将处理时区，因此让我们为PHP提供一个默认时区。 在您的php.ini配置文件中(我有一个用于CLI，一个用于Apache)，找到如下所示的部分：

[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().

如果没有给date.timezone ，PHP将尽力确定服务器上设置的系统时区。 您可以使用date_default_timezone_get()检查PHP使用的值。

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.

让我们将服务器的时区设置为UTC时间( date.timezone = UTC )并保存配置文件。 您必须重新启动Apache或CLI外壳才能看到更改。

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 DateTime对象包括一个内部DateTimeZone类实例，以跟踪时区。 创建新的DateTime实例时，应将内部DateTimeZone设置为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.

现在，您可以看到两个DateTime对象被赋予不同时区的时间差异。 例如，这是一个将UTC转换为America / New_York(EST)时间的示例。

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).

请注意12月份的-0500偏移量。 如果您将时间值更改为夏季日期(例如7月1日)，则会看到夏令时(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

在MySQL和PHP中使用日期 (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.*

如果您曾经使用过MySQL的任何级别，则可能已经注意到开箱即用的DATETIME类型。 它看起来和闻起来像一个约会，如果您说那是一个约会，那您将是对的。 但是，一旦您将SELECT从MySQL转换到PHP中之后，您真正拥有的只是一个看起来像日期的字符串。 它没有时区意识，并且已经被格式化以供人类消费，然后再需要人类查看。*

* 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.

*是的，我知道MySQL具有大量的日期格式化功能，但是我们也已经在处理PHP，正如您所看到的那样，PHP会处理日期格式。 为什么我们仍然要从数据库中直接格式化它？ 我们可能要应用一些不同的转换和格式。 最好仅在有人要看到日期时格式化日期。

<?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.

运行此简单脚本，您可以看到DATETIME字段中的所有内容都是带格式的字符串，没有时区信息。

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.

DATETIME值是在任何时区运行MySQL的服务器的本地时间。 如果您所做的一切仅涉及一个时区中的一台服务器，则DATETIME可能会满足您的大多数需求，我非常嫉妒您。

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

那么如何用PHP和MySQL处理日期和时区呢？ 将日期存储为Unix时间戳。

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.

您已经知道Unix时间是UTC自1970年1月1日以来的秒数，因此可以为您提供一个恒定的时区，并且希望您已经注意到许多PHP的日期/时间函数都基于Unix。时间戳记。

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时，我通常会创建表列，将日期存储为INTEGER UNSIGNED 。 插入日期时，可以使用PHP中的time()或MySQL中的UNIX_TIMESTAMP() 。

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格式化日期，则可以。 但是时间将在服务器的时区中，我建议您不要使用任何类型的格式设置，直到您处于Web应用程序的模板/视图级别并准备好让人眼可以看到它为止。

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值。 假设您有一个如下所示的会话条目：

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

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

您可以轻松地将存储的Unix时间(以UTC为单位)转换为特定用户所在时区中的任何日期。

<?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.

这将导致日期为“ 2012年1月16日星期一12:03:49 -0600”。 -0600告诉您，它比UTC晚6个小时，该时间的偏移量为0。

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

如果我们将时区设置更改为America / Los_Angeles，则生成的日期为：

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

And America/New_York would yield:

而America / New_York将产生：

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.

处理日期和时区是许多程序员的日常工作，但是，当您使用PHP强大且易于使用的日期库时，不必担心。

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.

您已经了解了获得Unix时间戳有多么容易，如何将日期格式化为任何可以想象的格式，如何将日期的英文表示形式解析为时间戳，如何为时间戳添加时间段，以及如何在时区之间进行转换。 如果本文有两个要点，那就是：1)坚持使用Unix时间，2)坚持使用UTC作为使用PHP和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?”

始终基于UTC的想法并非只适用于PHP和MySQL。 任何语言都被认为是好的做法。 而且，如果您发现自己使用另一种语言工作，那么您很有可能会对自己说：“该死，为什么他们不能像PHP那样呢？”

Image via Yakobchuk Vasyl / Shutterstock

图片来自Yakobchuk Vasyl / Shutterstock

翻译自: https://www.sitepoint.com/working-with-dates-and-times/