我有数千个日期，格式如下:

2011-10-02T23:25:42Z(在 UTC 中也称为 ISO 8601)

我应该使用哪种 MySQL 数据类型在 MySQL 数据库中存储这样的 ISO8601 日期？例如。 Datetime、timestamp 还是别的什么？

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

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

最佳答案

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

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

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

为 PHP 使用读取 DATETIME 值

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

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

1. 使用 DateTime 类对象的 format 方法将 PHP DateTime 类对象转换为 UTC 格式字符串表示形式的 ISO 8601，与之前相同'Y-m-d\TH:i:s\Z' 格式化字符串 ( documentation ).
2. 使用准备好的字符串作为 MySQL 函数 STR_TO_DATE 的参数对数据库信息执行 INSERT/UPDATE 操作(with '% Y-%m-%dT%H:%i:%sZ' 格式化字符串)将其转换为真实数据库 DATETIME 值( docs on STR_TO_DATE ) .

PHP 示例代码

请在下面找到使用 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
}

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

希望对您也有帮助。

关于php - 如何在 MySQL 数据库中存储 UTC ISO8601 日期？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/8047462/