如何在UTC数据库中存储UTC ISO8601日期？ [英] How do I store an UTC ISO8601 date in a MySQL database?

问题描述

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

I have thousands of dates in the following format:

2011-10-02T23：25：42Z （也就是UTC的ISO 8601）

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

MySQL数据类型应该用于在MySQL数据库中存储这样的ISO8601日期吗？例如。 Datetime ， timestamp 或其他内容？

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

哪个最适合进行比较（例如，在两个日期/时间之间获取记录）并从查询中排序结果？如果数据库非常大，该怎么办？

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?

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

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

推荐答案

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

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

根据我目前的PHP应用程序的经验，只有读取 / 写入有关此信息的操作可能是

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

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

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

1. 获取 DATETIME 您的数据库中的字段将它们在查询中转换为字符串表示形式为'2011-10-02T23：25通过使用 DATE_FORMAT 使用'％Y-％m-％dT％H：％i的MySQL函数：42Z' ％sZ'格式化字符串（ DATE_DATE_FORMAT ）

以这种特定格式读取获取的列值，并将其从PHP转换为字符串到PHP的实际日期时间表示（例如 DateTime 类对象和 DateTime :: createFromFormat 静态方法给定'Ymd\TH：i：s\Z'格式化字符串（ T 和 Z 被转义，以避免将它们视为格式化指令）（ docs for the method ）。

2. 将转换后的值用作实际的日期时间值，逻辑适用，如实时比较（不是文本比较）等。

1. Acquire DATETIME fields from your database converting them in the query to string representation in the form of '2011-10-02T23:25:42Z' by using DATE_FORMAT MySQL 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 DateTime class objects and DateTime::createFromFormat static method given 'Y-m-d\TH:i:s\Z' formatting string (T and Z are 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.

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

Writing PHP date-time to MySQL database

1. 将UTC对象转换为ISO 8601，以UTC格式转换为PHP 类对象使用 DateTime 类对象的格式方法与之前相同'Ymd\TH：i：s\\ \\Z'格式化字符串（ 文档 ） 。


2. 使用这样准备的字符串执行 INSERT / UPDATE 对数据库信息的操作作为MySQL函数的参数 STR_TO_DATE （含'％Y-％m-％dT％H：％i：％sZ' formatting string）将其转换为实际数据库 DATETIME value（ 文章STR_TO_DATE ）

1. Convert i.e. PHP DateTime class object to our ISO 8601 in UTC format string representation using DateTime class object's format method with the same as before 'Y-m-d\TH:i:s\Z' formatting string (documentation).
2. Perform INSERT / UPDATE operation 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 DATETIME value (docs on STR_TO_DATE).

PHP中的示例代码

以下请使用PDO对象查找此类方法的草案示例：

Example code in PHP

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

$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
}

这种方法帮助我在PHP和MySQL数据库之间运行日期时间值。

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

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

I hope it might occur helpful for you also.

这篇关于如何在UTC数据库中存储UTC ISO8601日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！