如何在MySQL数据库中存储协调时ISO8601日期？

Question

我有数以千计的日期，格式如下：

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

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

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

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

Answer 1

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

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

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

读取日期时间值以供PHP使用

1. 从数据库中获取DATETIME字段，将它们在查询中的转换为'2011-10-02T23:25:42Z'形式的字符串表示形式，方法是使用带有PHP格式化字符串()
2. Read此特定格式的获取列值的MySQL函数，并在'%Y-%m-%dT%H:%i:%sZ'中将其从字符串转换为对'2011-10-02T23:25:42Z'有效的实时日期表示形式(例如，给定D17格式字符串的DateTime类对象和DateTime::createFromFormat静态方法(D18和D19被转义以避免将它们视为格式化指令)(C20)。
3. 使用转换后的值作为实时日期-时间值，例如实际日期比较(不是文本比较)，等等。

将PHP date-time写入MySQL数据库

1. 使用DateTime类对象的format方法将DateTime类对象转换为使用UTC格式字符串表示的ISO8601，该方法与之前对数据库信息进行的().
2. Perform格式化字符串INSERT / UPDATE操作相同，使用这样准备好的字符串作为MySQL函数STR_TO_DATE的参数(使用<代码>D37格式字符串)，该函数将值转换为实际数据库<代码>D38值

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数据库之间操作日期-时间值有很大帮助。

我希望它的发生也能对你有所帮助。

Answer 2

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

使用CAST函数将这些字符串转换为mysql DateTime类型。

下面是一个示例：

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

这将为您提供2011-10-02 23:25:42。

希望这能对你有所帮助。

Answer 3

您可以使用php的strtotime函数轻松转换日期：

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

现在，您只需使用timestamp或datetime将日期插入到MySQL中，这取决于哪一个最适合您的需求。下面是关于这两种类型你应该知道的最重要的事情。

时间戳

受时区设置影响的

• 范围从'1970-01-01 00:00:01‘UTC到'2038-01-09 03:14:07’UTC
• 。对于所有versions.
• Index的列，faster
• NULL不是可能的默认值
• 4字节on update current_timestamp
• 值将从当前时区转换为UTC以进行存储，并从UTC转换回当前时区以进行检索。

Datetime

从5.6.5版本开始，从'1000-01-01 00:00:00‘到'9999-12-31 23:59:59'

• Constant (时区不会影响)的

• 范围仅对列进行

• 8字节storage

• allow更新

最适合比较的

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

根据我前面提到的几点，那么对于一个非常大的数据库，你应该使用timestamp，因为它的存储空间更小，索引更快，这将为你提供更好的性能进行比较。然而，你的必须确保你的日期将符合我前面提到的timestamp的限制，否则你别无选择，必须使用datetime。

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

而且，为了那些每天都在重复不建议使用mysql* 已弃用的函数的回答者，请在执行插入操作时使用PDO或mysqli*。

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

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