php 如何在 MySQL 数据库中存储 UTC ISO8601 日期？

How do I store an UTC ISO8601 date in a MySQL database?

提问by user967144

I have thousands of dates in the following format:

我有以下格式的数千个日期：

2011-10-02T23:25:42Z(aka ISO 8601 in UTC)

2011-10-02T23:25:42Z（在 UTC 中又名 ISO 8601）

What MySQL data type should I use for storing such a ISO8601 date in a MySQL database? E.g. Datetime, timestampor something else?

我应该使用什么 MySQL 数据类型来在 MySQL 数据库中存储这样的 ISO8601 日期？例如Datetime，timestamp或其他什么？

Which is best for comparison (eg. getting records between two dates/times) and ordering the results from queries? What about if the database is very large?

哪个最适合比较（例如，获取两个日期/时间之间的记录）和对查询结果进行排序？如果数据库很大怎么办？

And what would be the best way to convert the above PHP string for MySQL storage? (I'm guessing date_default_timezone_set('UTC');would be used?)

将上述 PHP 字符串转换为 MySQL 存储的最佳方法是什么？（我猜date_default_timezone_set('UTC');会被使用？）

回答by user967144

I think that keeping your date-time values in field of type DATETIMEwould be kind of natural way.

我认为将日期时间值保留在类型字段DATETIME中将是一种自然的方式。

From my own experience with my current PHP application, only read/ writeoperations concerning this information may be problematic.

根据我自己对当前 PHP 应用程序的经验，只有read/write与此信息相关的操作可能会出现问题。

One of possible solutions (assuming that you use DATETIMEdata type) for properly performing the whole process could be the following approach:

DATETIME正确执行整个过程的可能解决方案之一（假设您使用数据类型）可能是以下方法：

Reading DATETIME values for PHP use

读取 DATETIME 值以供 PHP 使用

1. Acquire DATETIMEfields from your database converting them in the queryto string representation in the form of '2011-10-02T23:25:42Z'by using DATE_FORMATMySQL function with '%Y-%m-%dT%H:%i:%sZ'formatting string (docs on DATE_FORMAT)
2. Read fetched column value in this specific format and convert it in PHP from string to real date-time representation valid for PHP (such as DateTimeclass objects and DateTime::createFromFormatstatic method given 'Y-m-d\TH:i:s\Z'formatting string (Tand Zare escaped to avoid treating them as formatting directives) (docs for the method).
3. Use converted values as real date-time values with all the logic applicable, like real date comparisons (not text-comparisons), etc.

1. DATETIME从您的数据库中获取字段，通过使用带有格式化字符串的MySQL 函数将查询中的字段转换为字符串表示形式（DATE_FORMAT 上的文档）'2011-10-02T23:25:42Z'DATE_FORMAT'%Y-%m-%dT%H:%i:%sZ'
2. 以这种特定格式读取获取的列值，并将其在 PHP 中从字符串转换为对 PHP 有效的实际日期时间表示（例如给定格式化字符串的DateTime类对象和DateTime::createFromFormat静态方法'Y-m-d\TH:i:s\Z'（T并Z进行转义以避免将它们视为格式化指令）（文档为方法）。
3. 将转换后的值用作具有所有适用逻辑的实际日期时间值，例如实际日期比较（而不是文本比较）等。

Writing PHP date-time to MySQL database

将 PHP 日期时间写入 MySQL 数据库

1. Convert i.e. PHP DateTimeclass object to our ISO 8601 in UTC format string representation using DateTimeclass object's formatmethod with the same as before 'Y-m-d\TH:i:s\Z'formatting string (documentation).
2. Perform INSERT/ UPDATEoperation on database information using such prepared string as a parameter for MySQL function STR_TO_DATE(with '%Y-%m-%dT%H:%i:%sZ'formatting string) which converts it to real database DATETIMEvalue (docs on STR_TO_DATE).

1. DateTime使用DateTime类对象的format方法将 ie PHP类对象转换为我们的 UTC 格式字符串表示形式的 ISO 8601，与'Y-m-d\TH:i:s\Z'格式化字符串之前的方法相同（文档）。
2. 使用这种准备好的字符串作为 MySQL 函数的参数（带有格式化字符串）对数据库信息执行INSERT/UPDATE操作，该函数将其转换为真实的数据库值（STR_TO_DATE 上的文档）。STR_TO_DATE'%Y-%m-%dT%H:%i:%sZ'DATETIME

Example code in PHP

PHP 中的示例代码

Below please find a draft example of such approach using PDO objects:

请在下面找到使用 PDO 对象的这种方法的草稿示例：

$db = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    // run the query aquring 1 example row with DATETIME data 
    // converted with MySQL DATE_FORMAT function to its string representation 
    // in the chosen format (in our case: ISO 8601 / UTC)
    $stmt = $db->query("SELECT DATE_FORMAT(dt_column, '%Y-%m-%dT%H:%i:%sZ') AS formatted_dt_col"
                        ." FROM your_table LIMIT 1"); 

    if($stmt !== FALSE) {
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // convert the acquired string representation from DB 
        // (i.e. '2011-10-02T23:25:42Z' )
        // to PHP DateTime object which has all the logic of date-time manipulation:    
        $dateTimeObject = DateTime::createFromFormat('Y-m-d\TH:i:s\Z', $row['formatted_dt_col']);

        // the following should print i.e. 2011-10-02T23:25:42Z
        echo $dateTimeObject->format('Y-m-d\TH:i:s\Z');  

        // now let's write PHP DateTime class object '$dateTimeObject' 
        // back to the database
        $stmtInsertDT = $db->prepare("INSERT INTO your_table(dt_column) " 
                             . " VALUES ( STR_TO_DATE(:par_formatted_dt_column, '%Y-%m-%dT%H:%i:%sZ') )");

        $dtAsTextForInsert = $dateTimeObject->format('Y-m-d\TH:i:s\Z');

        // convert '$dateTimeObject' to its ISO 8601 / UTC text represantation
        // in order to be able to put in in the query using PDO text parameter
        $stmtInsertDT->bindParam(':par_formatted_dt_column', $dtAsTextForInsert, PDO::PARAM_STR);

        $stmtInsertDT->execute();

        // So the real insert query being perform would be i.e.:
        /*
           INSERT INTO your_table(dt_column) 
           VALUES ( STR_TO_DATE('2011-10-02T23:25:42Z', '%Y-%m-%dT%H:%i:%sZ') )
        */
    }
}
catch(\PDOException $pexc) {
 // serve PDOException
}
catch(\Exception $exc) {
// in case of no-PDOException, serve general exception
}

This approach helped me a lot in operating date-time values between PHP and MySQL database.

这种方法对我在 PHP 和 MySQL 数据库之间操作日期时间值有很大帮助。

I hope it might occur helpful for you also.

我希望它也可能对您有所帮助。

回答by Ashish Awasthi

You can use DateTimedata type for storing the date and time.

您可以使用DateTime数据类型来存储日期和时间。

Use CASTfunction to cast such strings into mysql DateTimetype.

使用CAST函数将此类字符串转换为 mysqlDateTime类型。

Here is an example:

下面是一个例子：

CAST("2011-10-02T23:25:42Z" AS DATETIME)

This will give you 2011-10-02 23:25:42.

这会给你2011-10-02 23:25:42。

Hope this will help you.

希望这会帮助你。

回答by Jean-Fran?ois Savard

You can easily convert the date using strtotimefunction of php:

您可以使用以下strtotime功能轻松转换日期php：

date_default_timezone_set('UTC');
$date = '2011-10-02T23:25:42Z';//(aka ISO 8601 in UTC)
$time = strtotime($date); //time is now equals to the timestamp
$converted = date('l, F jS Y \a\t g:ia', $time); //convert to date if you prefer, credit to Marc B for the parameters

Now you would simply insert your date in MySQLusing timestampor datetimedepending on which one fit the most your needs. Here the most important things you should know about both types.

现在，您只需在MySQL使用中timestamp或datetime根据哪一个最适合您的需要插入您的日期。这里是您应该了解的关于这两种类型的最重要的事情。

---

---

Timestamp

时间戳

• Range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
• Affected by the time-zone setting.
• 4 bytes storage
• allow on update current_timestampon columns for all versions.
• Index is way faster
• NULLis not a possible default value
• Values are converted from the current time zone to UTCfor storage, and converted back from UTCto the current time-zone for retrieval.

• '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC 的范围
• 受时区设置影响。
• 4 字节存储
• 允许on update current_timestamp所有版本的列。
• 索引更快
• NULL不是可能的默认值
• 值从当前时区转换UTC为存储，并从当前时区转换回UTC以进行检索。

---

---

Datetime

约会时间

• Range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
• Constant (time-zone won't affect)
• 8 bytes storage
• allow update on columns only as of version 5.6.5

• “1000-01-01 00:00:00”到“9999-12-31 23:59:59”的范围
• 常量（时区不会影响）
• 8 字节存储
• 只允许更新列的版本 5.6.5

Which is best for comparison (eg. getting records between two dates/times) and ordering the results from queries? What about if the database is very large?

哪个最适合比较（例如，获取两个日期/时间之间的记录）和对查询结果进行排序？如果数据库很大怎么办？

According to the previous points I stated, then you should use timestampfor a very large database as the storage is smaller, and the index faster which will give you better performance for comparison. However, you MUST MAKE SUREyour date will fit the limits of the timestampI previously mentioned, else you have no choice and must use datetime.

根据我前面的观点，那么你应该timestamp用于一个非常大的数据库，因为存储更小，索引更快，这会给你更好的比较性能。但是，您必须确保您的日期符合timestamp我之前提到的限制，否则您别无选择，必须使用datetime.

Documentation for strtotime?: http://php.net/manual/en/function.strtotime.php

strtotime? 的文档：http: //php.net/manual/en/function.strtotime.php

And please, for the sake of SO's answerer who keep repeating every day to not use the mysql*DEPRECATEDfunctions, please use PDOor mysqli*when you will do your inserts.

并且，为了 SO 的回答者每天不断重复不使用已mysql*弃用的功能，请使用PDO或mysqli*何时进行插入。

http://php.net/manual/en/book.pdo.php

http://php.net/manual/en/book.pdo.php

http://php.net/manual/en/book.mysqli.php

http://php.net/manual/en/book.mysqli.php

回答by Electronick

You can not store date in raw UTC ISO8601 format (with 2011-10-02T23:25:42Zrepresentation) and save all SQL DATETIME functionality.

您不能以原始 UTC ISO8601 格式（带2011-10-02T23:25:42Z表示）存储日期并保存所有 SQL DATETIME 功能。

But you should know, that MySQL ( regarding to http://dev.mysql.com/doc/refman/5.5/en/datetime.html) always store time/date in UTC. Also you can modify timezone for your connection http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

但您应该知道，MySQL（关于http://dev.mysql.com/doc/refman/5.5/en/datetime.html）总是以 UTC 格式存储时间/日期。您也可以为您的连接修改时区 http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

So, if you execute in PHP

所以，如果你在 PHP 中执行

date_default_timezone_set('UTC');

and in MySQL

在 MySQL 中

SET time_zone = +00:00

sure PHP and MySQL would use UTC.

确定 PHP 和 MySQL 会使用 UTC。

After that you can convert all database strings to DateTime without caring about timezone mismatch.

之后，您可以将所有数据库字符串转换为 DateTime，而无需关心时区不匹配。

To convert any PHP DateTime (without carrying about its internal timezone) to MySQL datetime string you should set DateTime object timezone to UTC.

要将任何 PHP DateTime（不携带其内部时区）转换为 MySQL 日期时间字符串，您应该将 DateTime 对象时区设置为 UTC。

$datetime->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');

回答by CraigC

Using your datetime on my system which is PDT:

在我的 PDT 系统上使用您的日期时间：

SELECT CONVERT_TZ(str_to_date('2011-10-02T23:25:42Z','%Y-%m-%dT%H:%i:%sZ'),'+00:00','SYSTEM') from dual;

2011-10-02 16:25:42

If your datetime has a fractional microsecond; include the .%f before the Z as follows:

如果您的日期时间有小数微秒；在 Z 之前包含 .%f ，如下所示：

SELECT CONVERT_TZ(str_to_date('2011-10-02T23:25:42.123456Z','%Y-%m-%dT%H:%i:%s.%fZ'),'+00:00','SYSTEM') from dual;

2011-10-02 16:25:42.123456

回答by Gfox

Here are the points why it is better to use datetime.

以下是为什么最好使用日期时间的要点。

1. With datetime you will be able to do date manipulations on mysql side - such as subtracting day,month
2. You will be able to sort data.
3. If DB is huge - varchar takes more place on HDD

1. 使用 datetime，您将能够在 mysql 端进行日期操作 - 例如减去日、月
2. 您将能够对数据进行排序。
3. 如果数据库很大 - varchar 在 HDD 上占据更多位置