Как сохранить дату UTC ISO8601 в базе данных MySQL?

Ответ 1

Я думаю, что сохранение ваших значений даты в поле типа DATETIME было бы естественным способом.

Из моего собственного опыта работы с моим текущим PHP-приложением только операции read/write, касающиеся этой информации, могут быть проблематичными.

Одним из возможных решений (при условии, что вы используете тип данных DATETIME) для правильного выполнения всего процесса может быть следующий подход:

Чтение значений DATETIME для использования PHP

• Получите DATETIME поля из вашей базы данных, преобразуя их в запрос в строковое представление в форме '2011-10-02T23:25:42Z' с помощью DATE_FORMAT функции MySQL с строкой форматирования '%Y-%m-%dT%H:%i:%sZ' (docs на DATE_FORMAT)
• Прочитать полученное значение столбца в этом конкретном формате и преобразовать его в PHP из строки в реальное представление даты-времени, действительное для PHP (например, объекты класса DATETIME и DateTime::createFromFormat статический метод с заданной строкой форматирования 'Y-m-d\TH:i:s\Z' ( и Z, чтобы избежать обработки их как директив форматирования) (docs для метода).
• Использовать преобразованные значения в качестве реальных значений даты и времени со всеми применимыми логическими параметрами, такими как сопоставление реальной даты (не сравнение текста) и т.д.

Написание PHP date-time для базы данных MySQL

• Преобразовать объект класса PHP DATETIME в наш ISO 8601 в строчном представлении формата UTC с использованием метода DATETIME class object format с тем же значением, что и перед строкой форматирования 'Y-m-d\TH:i:s\Z' (документация).
• Выполните операцию INSERT/UPDATE по информации базы данных с использованием такой подготовленной строки в качестве параметра для функции MySQL STR_TO_DATE (с строкой форматирования '%Y-%m-%dT%H:%i:%sZ'), которая преобразует ее в реальную базу данных DATETIME value (docs на 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 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.

Я надеюсь, что это может оказаться полезным и для вас.

Ответ 2

Вы можете использовать тип данных DateTime для хранения даты и времени.

Используйте функцию CAST, чтобы вывести такие строки в тип mysql DateTime.

Вот пример:

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

Это даст вам 2011-10-02 23:25:42.

Надеюсь, это поможет вам.

Ответ 3

Вы можете легко преобразовать дату с помощью функции 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

Теперь вы просто вставляете свою дату в MySQL с помощью timestamp или datetime в зависимости от того, какой из них наиболее подходит вашим потребностям. Здесь самые важные вещи, которые вы должны знать обо всех типах.

Отметка

• Диапазон '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
• Затронуто настройкой часового пояса.
• 4 байта хранения
• разрешить on update current_timestamp для столбцов для всех версий.
• Индекс быстрее.
• NULL не является возможным значением по умолчанию
• Значения преобразуются из текущего часового пояса в UTC для хранения и преобразуются обратно из UTC в текущую временную зону для извлечения.

Datetime

• Диапазон "1000-01-01 00:00:00" до "9999-12-31 23:59:59"
• Константа (временная зона не влияет)
• 8-байтовая память
• разрешить обновление по столбцам только с версии 5.6.5

Что лучше для сравнения (например, получение записей между двумя даты/времени) и упорядочивание результатов запросов? Как насчет того, база данных очень большая?

В соответствии с предыдущими пунктами, которые я изложил, вы должны использовать timestamp для очень большой базы данных, поскольку хранилище меньше, а индекс быстрее, что даст вам лучшую производительность для сравнения. Тем не менее, вы ДОЛЖНЫ БЫТЬ УВЕРЕНЫ, ваша дата будет соответствовать ограничениям timestamp, о которых я упоминал ранее, иначе у вас нет выбора и вы должны использовать datetime.

Документация для strtotime: http://php.net/manual/en/function.strtotime.php

И, пожалуйста, ради SO-ответчика, которые каждый день повторяют, чтобы не использовать функции mysql* DEPRECATED, используйте PDO или mysqli*, когда будете делать свои вставки.

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

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

Ответ 4

Вы не можете сохранить дату в необработанном формате UTC ISO8601 (с представлением 2011-10-02T23:25:42Z) и сохранить все функциональные возможности SQL DATETIME.

Но вы должны знать, что 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

Итак, если вы выполняете в PHP

date_default_timezone_set('UTC');

и в MySQL

SET time_zone = +00:00

уверен, что PHP и MySQL будут использовать UTC.

После этого вы можете преобразовать все строки базы данных в DateTime, не заботясь о несоответствии часового пояса.

Чтобы преобразовать любой PHP DateTime (без учета внутреннего часового пояса) в строку MySQL datetime, вы должны установить часовой пояс объекта DateTime в UTC.

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

Ответ 5

Используя вашу дату и время в моей системе, которая называется 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

Если ваша дата и время имеют дробную микросекунду; включите.% f перед Z следующим образом:

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

Ответ 6

Вот то, почему лучше использовать datetime.

• С datetime вы сможете делать манипуляции с датами на стороне mysql - например, вычитать день, месяц
• Вы сможете сортировать данные.
• Если DB огромна - varchar занимает больше места на жестком диске