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

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

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

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

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

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

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

1. Преобразуйте, т.е. объект класса PHP DateTime в наш ISO 8601 в 'Ymd\TH:i:s\Z' представлении формата UTC с использованием метода format объекта DateTime с тем же значением, что и перед 'Ymd\TH:i:s\Z' ( документация ).
2. Выполните операцию INSERT / UPDATE в базе данных с использованием такой подготовленной строки в качестве параметра для функции MySQL STR_TO_DATE (с STR_TO_DATE '%Y-%m-%dT%H:%i:%sZ' ), которая преобразует ее в реальную базу данных. Значение DATETIME ( документы на 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 // (ie '2011-10-02T23:25:42Z' ) // to PHP DateTime object which has all the logic of date-time manipulation: $dateTimeObject = DateTime::createFromFormat('Ymd\TH:i:s\Z', $row['formatted_dt_col']); // the following should print ie 2011-10-02T23:25:42Z echo $dateTimeObject->format('Ymd\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('Ymd\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 ie: /* 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.

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

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

Используйте функцию CAST для перевода таких строк в тип mysql DateTime .

Вот пример:

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

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

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

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

Вы не можете хранить дату в необработанном формате 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/ru/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('Ymd H:i:s'); 

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

1. С datetime вы сможете делать манипуляции с датами на стороне mysql – например, вычесть день, месяц
2. Вы сможете сортировать данные.
3. Если БД огромна – varchar занимает больше места на HDD