Записать в mysql текущую дату и время. Преобразование даты в mysql с помощью DATE_FORMAT()

Для начала, хочу затронуть тему о том, в каком формате лучше хранить даты в базе: TIMESTAMP или DATETIME . Этот вопрос неоднократно поднимался и поднимается на форумах, блогах и т.д. Но дабы не отправлять вас сразу же в поисковики, я попробую простыми словами и на примере показать разницу. Тип DATETIME - хранит значение даты в формате "YYYY-MM-DD HH:MM:SS" и не зависит от часового пояса. TIMESTAMP - хранит метку времени, т.е. количество секунд, прошедших с 1 января 1970-го года. Эти значение, MySQL преобразует с учётом текущего часового пояса как при записи в базу, так и при выводе из неё. Что сие значит...
К примеру, вы только что добавили в базу статью, на календаре у вас первое января 2014 года, а на часах - 01:00. Если поле даты имеет тип DATETIME, то все, кто бы ни зашёл на сайт, увидят именно эту дату и время, не зависимо от их места проживания. Вроде бы всё нормально, но у пользователя (назовём его "Билл Г" ), проживающего где-нибудь в Нью-Йорк, еще не наступило первое января - у него 31 декабря 2013 года и часы показывают 19:00. У него лёгкое недоумение, т.к. праздновать новый год он ещё не начал, а уже мерещиться "статья из будущего" ;) С типом TIMESTAMP этого не произойдёт, т.к. при выводе будет учитываться его часовой пояс.
"Всё ясно!", - скажете вы и быстренько измените все поля для дат на тип TIMESTAMP, а Билл Г вздохнет с облегчением, но ненадолго. При регистрации на вашем сайте, Билл указал дату и время своего рождения. Путешествуя по миру, он обязательно заглядывает на ваш сайт и с ужасом обнаруживает, что время, а иногда и дата его рождения, всегда разные, т.к. выводятся с учетом часового пояса, в котором он находится в данный момент. Да, в этом случае, тип TIMESTAMP сыграл злую шутку.
Делаем вывод - для определённых задач, нужно выбирать соответствующий тип поля или контролировать запись/вывод в зависимости от желаемого результата.

Переходим к популярным задачам и вариантам их решения. Выбрать записи в диапазоне указанных дат, т.е. за определенный период времени.

SELECT * FROM `table_name` WHERE `date_field` BETWEEN "2014-07-05" AND "2014-07-15" ORDER BY `date_field`;

Выберутся все записи, где даты в поле "date_field", будут в диапазоне от 5 июля 2014 года до 15 июля 2014, включая указанные даты. Нужно не забывать, что по умолчанию даты в MySQL хранятся в формате "ГГГГ-ММ-ДД ЧЧ:ММ:СС" и соответственно маска формата - "%Y-%m-%d %H:%i:%s" (стандарт ISO ). А как решать вопрос, если дата приходит не в таком формате? Отбросим варианты с PHP и посмотрим, как это можно сделать в самом запросе. А для таких целей, нам пригодится функция STR_TO_DATE() . Синтаксис: STR_TO_DATE(str, format) , где "str " - строка даты и "format " - соответствующий ей формат. Протестируем:

SELECT STR_TO_DATE("31.12.2013", "%d.%m.%Y"); /* "2013-12-31" */ SELECT STR_TO_DATE("31/12/13 13:50", "%d/%m/%y %H:%i"); /* "2013-12-31 13:50:00" */

Результат выполнения - дата в формате, который используется в MySQL по умолчанию. То есть, нам надо указать не формат, в котором мы хотим получить дату на выходе, а формат, в котором мы предоставляем дату для обработки. Используя такой способ, наша запись выше, могла бы выглядеть даже так:

SELECT * FROM `table_name` WHERE `date_field` BETWEEN STR_TO_DATE("05.07.2014", "%d.%m.%Y") AND STR_TO_DATE("July 15, 2014", "%M %d,%Y") ORDER BY `date_field`;

Раз уж затронули вопрос форматирования дат, то давайте разберем то, как получать дату при выборке в нужном нам формате, т.к. многим гораздо привычней видеть "31.12.2014" или "31 декабря 2014", чем "2014-12-31". Для таких целей используют функцию DATE_FORMAT() . Синтаксис: DATE_FORMAT(date, format) , где "date " - строка даты и "format " - формат, в который необходимо преобразовать "date ". В отличии от функции STR_TO_DATE(), мы сами указываем желаем формат на выходе, а вот дату нужно указывать в формате ISO, т.е. "ГГГГ-ММ-ДД ЧЧ:ММ:СС". Проверяем:

SELECT DATE_FORMAT("2014-12-31", "%d.%m.%Y"); // 31.12.2014 SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // 31 December 2014

Если бы мы общались с вами в реальном времени, то в этом месте, скорее всего, что сразу последовал бы вопрос: "А как выводить месяц на другом языке: украинском, русском или китайском, в конце концов? " Очень просто - установить необходимую локаль . А сделать это можно или же в конфигурационном файле MySQL (my.cnf), или же просто запросом из PHP, после подключения к базе и перед основным запросов:

SET lc_time_names = ru_RU; SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // результат: 31 Декабря 2014 // при желании, можно добавить еще и "г." или "года" SELECT DATE_FORMAT("2014-12-31", "%d %M %Y года"); // результат: 31 Декабря 2014 года

Красотища! ;) И еще несколько примеров запросов, которые так же бывают часто нужны, но вызывают ступор у новичков.

// Выбрать записи за текущий день SELECT * FROM `table_name` WHERE `date_field` >= CURDATE(); // Все записи за вчерашний день SELECT * FROM `table_name` WHERE `date_field` >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `date_field` NOW() - INTERVAL 30 DAY; // Выбрать всё за определенный месяц текущего года (например, за май месяц) SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = 5; // или за май месяц, но в 2009 году SELECT * FROM `table_name` WHERE YEAR(`date_field`) = 2009 AND MONTH(`date_field`) = 5;

Подробно описывать используемые в примерах функции MySQL - я смысла не вижу, т.к. они интуитивно-понятны и для человека, хоть немного знакомого с английским, не составит труда понять, что, к примеру, функция MONTH() возвращает месяц даты, YEAR() - её год, а DAY() (или синоним DAYOFMONTH() ) - день. Ключевое слово INTERVAL - служит для арифметических действий над датами, их изменением.

SELECT "2014-07-07 23:59:59" + INTERVAL 1 SECOND; // результат: 2014-07-08 00:00:00 SELECT "2014-07-07 23:59:59" + INTERVAL 1 DAY; // результат: 2014-07-08 23:59:59 // то же самое. но с помощью функции DATE_ADD() SELECT DATE_ADD("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-08 23:59:59 // Если надо не добавить, а отнять SELECT DATE_SUB("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-06 23:59:59 // или так просто SELECT "2014-07-07 23:59:59" - INTERVAL 1 DAY; // 2014-07-06 23:59:59

Это далеко не все функции для работы с датами и я бы посоветовал вам пробежаться по ним в ознакомительных целях на официальном сайте для того, чтобы знать об их существовании, если возникнет нестандартная ситуация. Но хочу надеяться, что даже такой небольшой обзор функций MySQL для работы с датами в этой статье, поможет вам сориентировать в ситуации и принять правильное решение. Если всё-таки возникнут сложности, то задавайте вопросы в этой теме или разделе "Ваш вопрос ". Будем разбираться вместе;)

Как известно, все даты хранятся в mysql в обратном порядке год-месяц-число(2008-10-18), иногда даже без разделителя(20081018).
Чтобы вывести дату, нужно ее преобразовать в нормальный читаемый вид.

Тут есть два способа преобразования, эффективный и не очень.
Не эффктивный способ это когда дату выводимую из mysql преобразуют с помощью php.
Сам лично так делал очень долгое время. Перед выводом на экран переворачивал каждую дату с помощью php функции.
Если количество преобразований не большое, то можно переворачивать дату и с помощью php, ничего плохого тут нет, но если нужно будет выдернуть десятки-сотни тысяч записей и в каждой преобразовать дату, то тут конечно намного быстрее будет преобразование дат с помощью mysql.

В mysql существует отличная функция DATE_FORMAT(), она очень похожа на php функцию date().
Вот пример использования

SELECT DATE_FORMAT("2008-11-19","%d.%m.%Y");
результат

Все очень просто и быстро, нет необходимости крутить даты с помощью php.
Вот список определителей этой функции

Комментарии

27.11.2008 ----
Ай шайтан!!!
Сам занимаюсь лет пять php и mysql, и все время переделывал дату в php...
Даже в голову не приходило, что проще использовать втроенную функцию mysql

28.11.2008 Жека
Аналогично! Всегда пользовался своей функцией на php

03.12.2008 Сергей
Ну а вообще, кто то хоть пользуется таким подходом?
Или все используют php для переворота даты?
Я лично никогда не переворачивал дату в mysql, до сих пор все в php делаю

28.06.2009 Илья
К сожалению, ничего не получилось:(

08.07.2009 Виталий
Круто, спасибо за функцию. Интересно, а ёщё какие нибуть загогулины есть?

14.07.2009 DSaint
Спасибо, очень помогло. Осталось только название месяца на русском отобразить)

28.07.2009 Влад
mysql=>PHP
select unix_timestamp(start_date) as start_date_php
php-code
date("d.m.Y",$row["start_date_php"])

PHP=>MySQL
update table set start_date=DATE_FORMAT(STR_TO_DATE("19.12.2009 18:35:22","%d.%m.%Y %H:%i"),"%Y.%m.%d %H:%i")

18.08.2009 Гость
2: DSaint
Есть такая чудная функция:
ELT(MONTH("2004-04-10"), "Янв.","Фев.","Март","Апр.","Май","Июнь","Июль","Авг.","Сен.","Окт.","Ноя.","Дек.")

Пользуйся. :-)

29.10.2009 Владимир
Спасибо, про ELT(MONTH("2004-04-10"), "Янв.","Фев.","Март","Апр.","Май","Июнь","Июль","Авг.","Сен.","Окт.","Ноя.","Дек.")
я не слыхал.

07.10.2010 Евгений
А что быстрее работает? Преобразование в запросе, или в результате работы php-функции?

07.10.2010 defender
Как минимум меньше памяти на обработку меньше вызовов функций меньше выделений памяти... Постоянно подобным пользуюсь только не в mysql а в postgresql.

08.10.2010 Админ
Евгений, вот defender правильно сказал, через базу это преобразование должно сработать красивее, но конечно если речь идет об извлечении огромного количества данных.
Если вы дергаете 10-20 записей, то нет никакой разницы как преобразовать дату, по сравнению с остальными нагрузками это мелочь.

27.01.2011 pcemma
ухх спасибо аффтору (: отпала нужда юзать свою мега крутую функцию для преобразования (:

13.04.2011 Xes
кАК ЕЕ ПОЛЬЗОВАТЬ В ПХП
while ($sqlr=mysql_fetch_array($sql))
{
echo ($sqlr["comadd"]." ".$sqlr["comment"]."

");

$sqlr["comadd"] - Надо в нормальной форме представитт?
}

14.04.2011 Виталий
У меня в базе дата записана в формате 19.11.2008 тип таблицы VARCHAR, как ее перезаписать в базу в формате 2008-11-19?
Руками просто очень долго...
Спасибо.

15.04.2011 админ
Xes, это функция MySQL, использовать ее нужно в запросе sql, который судя по вашему коду находится где-то выше. В данном участке кода ее нельзя использовать.

15.04.2011 админ
Виталий, просто измените тип ячейки на DATE, mysql автоматически переведет все данные в этой ячейке именно в формат 2008-11-19.
Но на всякий случай перед изменением типа ячейки сделайте дамп этой таблицы, так как вдруг база что то не то сделает и таблица сломается вообще.

Если вам важно оставить тип поля varchar, то после того, как вы установите тип DATE, установите обратно varchar...

Это самый простой вариант, но не совсем правильный, но я проверил, он работает.

14.05.2011 DDD
date("d-m-Y",strtotime("$myrow"));

24.05.2011 Константин
а я всегда беру SELECT *,UNIX_TIMESTAMP(created) AS created FROM...
а мотом могу хоть в каком формате. Хоть только день, хоть только время...
и сравнивать, что больше 14.05.2011 или 14.05.2010...
А записываю так:
...date=".gmdate("Y-m-d H:i:s",$created)...
и вобщем-то, не вижу причин менять привычки

24.05.2011 сергей
Константин, да я и сам использую для вывода php date(), просто тут рассмотрен вариант преобразования даты не через php, а через mysql.

Я бы сказал, что это просто обзор mysql функции и она конечно имеет право на существование...

Значения в этих форматах:

Как строка в формате "YYYY-MM-DD" или "YY-MM-DD" . Разрешен "расслабленный" синтаксис: любой символ пунктуации может использоваться как разделитель между частями даты. Например, "2012-12-31" , "2012/12/31" , "2012^12^31" и "2012@12@31" эквивалентны.

Как строка без разделителей в формате "YYYYMMDD" или "YYMMDD" при условии, что строка имеет смысл в качестве даты. Например, "20070523" и "070523" интерпретируются как "2007-05-23" , но "071332" является незаконным (он имеет бессмысленные месячные и дневные части) и становится "0000-00-00" .

Как число в формате YYYYMMDD или YYMMDD , при условии, что число имеет смысл в качестве даты. Например, 19830905 и 830905 интерпретируются как "1983-09-05" .

Следовательно, строка "08/25/2012" не является допустимым литералом даты MySQL. У вас есть четыре варианта (в каком-то неопределенном порядке предпочтения, без дополнительной информации о ваших требованиях):

Настройте Datepicker для предоставления дат в поддерживаемом формате с помощью altField вместе с altFormat :

$("selector").datepicker({ altField: "#actualDate" altFormat: "yyyy-mm-dd" });

Или, если вы довольны тем, что пользователи видят дату в формате YYYY-MM-DD , просто установите вместо параметра dateFormat :

$("selector").datepicker({ dateFormat: "yyyy-mm-dd" });

• $dt = \DateTime::createFromFormat("m/d/Y", $_POST["date"]);

  а затем либо:

  получить подходящую форматированную строку:

  $date = $dt->format("Y-m-d");

  получить временную метку UNIX:

  $timestamp = $dt->getTimestamp();

  который затем передается непосредственно в MySQL FROM_UNIXTIME() :

  INSERT INTO user_date VALUES ("", "$name", FROM_UNIXTIME($timestamp))

Вручную введите строку в действительный литерал:

$parts = explode("/", $_POST["date"]); $date = "$parts-$parts-$parts";

Предупреждение

Ваш код уязвим для SQL-инъекции. Вы действительно должны использовать подготовленные заявления , в которые вы передаете свои переменные как параметры, которые не оцениваются для SQL. Если вы не знаете, о чем я говорю, или как это исправить, прочитайте историю Bobby Tables .

• Тип DATE используется для значений с частью даты, но без временной части. MySQL извлекает и отображает значения DATE в формате "YYYY-MM-DD" . Поддерживаемый диапазон составляет от "1000-01-01" до "9999-12-31" .

  Тип DateTime используется для значений, содержащих как дату, так и время. MySQL извлекает и отображает значения DateTime в формате "YYYY-MM-DD HH:MM:SS" . Поддерживаемый диапазон составляет от "1000-01-01 00:00:00" до "9999-12-31 23:59:59" .

MySql – это не просто привлекательная система для организации хранения данных, но и ОТЛИЧНОЕ СРЕДСТВО обработки информации.

Сегодня мы разберем работу с функциями mysql: Date, DateTime. Так же я расскажу о методах mysql применяемых для конвертирования строки в дату. B общем, разные подходы к решению задач связных датами в СУБД mysql.

Задачка.

Представим, вы пишите систему отправки смс-уведомлений по базе ваших клиентов.
Таблица 1. tbl_clients* — таблица с данными клиентов (Ф.И.О. и дата рождения), данными о количестве, виде товара и статусе обработки заявки.

Таблица 2. tbl_sms – таблица статусов смс и связи id клиента с датой получения статуса.

Ближе к боевым действиям. Вооружаемся встроенными функциями.

В таблице tbl_sms на один Id_clients записано несколько записей, с разными статусами и временем получения. Например:

Предположим, что начальник требует список последних статусов по всем клиентам. Быстро решить задачу можно применяя встроенные функции. Запрос sql для такого случая выглядит следующим образом:

SELECT * FROM tbl_sms ts JOIN (SELECT Id_clients, max(Date_status) as max_dat FROM tbl_sms GROUP BY Id_clients) ts_max ON ts. id_clients = ts_max. id_clients and ts. Date_status= ts_max.max_dat ;

Результат запроса — выборка записей с максимальной датой для каждого Id_clients:

Запрос сложный, я сам не с первого раза понял, но попытаюсь объяснить. В запросе мы объединяем две таблицы. Первая tbl_sms — содержит все данные без учета условий, так сказать в «сыром виде». Вторая таблица — результат запроса

SELECT Id_clients, max(Date_status) as max_dat FROM tbl_sms GROUP BY Id_clients ;

к той же tbl_sms с использование функции MAX() и группировкой по полю Id_clients.

Во второй таблице мы группируем записи по id_clients с помощью конструкции «GROUP BY Id_clients». То есть теперь у нас одна запись (хотя в таблице для одного значение id_clients присутствует несколько записей) – это одно значение поля id_clients. В SELECT мы используем функцию MAX(), результатом которой является выборка максимального значения записанных в поле таблицы. Для справки есть, в СУБД mysql обратная функция MIN(). В результате в запросе с использованием функции MAX() мы получаем максимальное значение поля Date_status для каждого значения поля id_clients.
И наконец, выбирая записи соответствующие следующим условиям: “ON ts. id_clients = ts_max. id_clients and ts. Date_status= ts_max.max_dat”, мы получаем желаемый результат.

Начальник, доволен. Но вот, приходит бухгалтер и ставит новую задачу: «Необходимо выбрать все статусы, которые мы получили, только за 05.10.2014 г.».

Пишем следующий запрос:

SELECT * FROM tbl_sms WHERE Date_status>’2014-10-05 00:00:00’ And Date_status<’2014-10-05 23:59:59’ ;

Так как тип данных поля Date_status – DATETIME, поэтому мы ставим следующие условия: значение От 00:00 и до 23:59 05.10.2014 –этот промежуток как раз и входят сутки или 24 часа. Следует обратить внимание на форму записи даты. В типе данных Date в mysql представляем дату в следующем формате Год(4)-Месяц(2)-День(2). Если бы поле «статус» имело формат DATE, то запрос имел бы следующий вид:

SELECT * FROM tbl_sms WHERE Date_status="2014-10-05";

В задаче бухгалтера мы разобрались. Отчеты сданы. Теперь рассмотрим работу с датами в mysql под другим углом — если дата представлена в виде строки. Бывает и такое…

Дата в типе VARCHAR или CHAR… Что делать? Использования функции преобразования строки.

Я сам не раз сталкивался с подобной проблемой. Дело в том, что если у вас дата в виде строки, то все выше описанные запросы становятся бесполезны. Например, нужно выбрать всех клиентов у кого значения поля Date_Zap лежит в определенном периоде дат. Не перечислять же все даты… Конечно нет. Выход есть всегда, и нас снова выручат встроенные функции. В для такой задачи в mysql, есть функция STR_TO_DATE().
Как уже понятно из названия, функция конвертирует строку в дату. Аргументы функции STR_TO_DATE: дата в типе данных CHAR или VARCHAR, и формат даты КОТОРУЮ ВЫ ПЕРЕДАЕТЕ.

Например, вы сделаете запрос к таблице tbl_clients:

SELECT Date_Zap, STR_TO_DATE(Date_Zap,’%d.%m.%Y’) FROM tbl_clients WHERE id_clietns=’345’;

Результат применения функции STR_TO_DATE() :

Как видите из привычного формата даты мы получаем дату формата mysql. Несколько комментариев по поводу запроса:

• Если вы до конца не уверены в результате работы функции всегда делайте как тут, чтобы видеть значения поля до преобразования и после. Вам это сэкономит нервы.
• Если вы собираетесь использовать результат, например, в программе и обращаться именно по имени поля, то советую заменить длинное название “STR_TO_DATE(Date_Zap,’%d.%m.%Y’)”, например, на название Data_Mysql для этого модифицируем запрос

Результат использования функции конвертирование строки в формат даты mysql (STR_TO_DATE()):

Отображение даты прописью.

Часто возникает такая необходимость, чтобы при создании отчетности из базы данных (mysql) на документах дата стояла в формате «День Месяц прописью Год». Например: есть “21.10.2014”, а надо “21 октября 2014”. Когда я столкнулся с такой проблемой, я писал целую обработку на Delphi, чтоб генерировать дату. Оказалось, что в СУБД mysql уже есть встроенная функция конвертирования даты из числового представления в дату прописью. И, как я говорил в начале, mysql — не только средство управления хранения данных, но и средство обработки информации.
Итак, получить дату в выше описанном формате легко в простом запросе:

SELECT DATE_FORMAT(‘2014-10-21’,’%d %M %Y’) as Data_Mysql; //Обратите внимание на написание %M

Все бы хорошо дата отображается прописью, но название месяца на английском. Такое может возникнуть, если на сервере глобальная переменная lc_time_names=en_EN. Значение для русской даты будет равно: lc_time_names=ru_RU.
Есть несколько вариантов решения данной задачи.
Первый вариант, можно прописать значение переменой по умолчанию в конфигурации сервера mysql. Для этого откройте my.ini в каталоге куда установлен сервер mysql, и в раздел «» вставите строку «lc_time_names = ‘ru_RU"».
Второй вариант, подойдет вам, если нет прямого доступа к конфигурации mysql. Значение переменой можно сменить при выполнении запроса sql. Просто перед запросом выполнить следующее: «SET lc_time_names = ‘ru_RU’;». Данной командой вы устанавливаете значение переменой lc_time_names в данной сессии с сервером mysql. То есть если вы потеряете связь с сервером mysql, при переподключении, она выставится по умолчанию на значение «en_EN».
Поэтому советую применять команду непосредственно перед выполнение запроса sql. В таком случае итоговый запрос примет вид:

SET lc_time_names = "ru_RU"; SELECT DATE_FORMAT(‘2014-10-21’,’%d %M %Y’) as ‘Data_Mysql’;

Результат работы запрос sql c использование функции DATE_FORMAT():

Как видите все стало корректно, название месяца стало русскоязычным.
Таким образом с помощью функций mysql легко и быстро обрабатывать информацию. Я вам советую не выполнять работу, которую вы можете перекинуть на средства mysql. Так вам меньше кода писать, и 90% случаев подобная система будет более стабильно работать. Проверено на собственном опыте. Надеюсь, данная статья будет вам полезна, Дорогие Читатели! С уважением, ваш Shinobi.

Итак, все календарные типы данных подробно описаны в разделе «10.3. Date and Time Types » руководства по MySQL. А важная информация, касающаяся поддержки СУБД временных зон, расписана в разделе «9.7. MySQL Server Time Zone Support ». Все следующее далее базируется на изучении руководства. В то же время, в здесь указаны лишь нюансы выбора в пользу того или иного типа, поэтому этот материал никак не заменяет мануал, но дополняет его.

Вначале краткая характеристика каждого из типов:

• TIMESTAMP - тип данных для хранения даты и времени. Данные хранятся в виде количества секунд, прошедших с начала «эпохи Юникса». Диапазон значений: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. Занимает 4 байта.
• YEAR - тип данных для хранения года. Диапазон значений: 1901 - 2155. Занимает 1 байт.
• DATE - тип данных для хранения даты. Диапазон значений: 1000-01-01 - 9999-12-31. Занимает 3 байта.
• TIME - тип данных для хранения времени. Диапазон значений: −828:59:59 - 828:59:59. Занимает 3 байта.
• DATETIME - тип данных для хранения даты и времени. Диапазон значений: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. Занимает 8 байт.

Проанализировав описание типов, представленное выше, можно сделать практически все выводы о достоинствах и недостатках тех или иных типов. Все довольно просто и очевидно.

Но прежде, чем рассказать об использовании этих типов, хочу заметить, что на практике часто используется другой тип для хранения даты и времени: целочисленное значение (для хранения даты - INT (4 байта), даты и времени - BIGINT (8 байт)). Отличие использования целочисленных типов от DATE и DATETIME лишь в том, что при выводе данные не форматируются, а в вычислениях с датами и временем целые числа требуется преобразовывать в соответствующий календарный тип. Кроме того, не производится проверка на валидность представленного значения перед сохранением. Возможности сортировки сохраняются. Поэтому INT и BIGINT имеет смысл использовать в тех же случаях, как DATE и DATETIME, с целью максимизации переносимости и независимости от СУБД. Других преимуществ я не вижу, если они есть, предлагаю указать в комментах.

Использование календарных типов данный в MySQL

Типы DATE и DATETIME можно объединить в одну группу. Они хранят дату или дату и время с довольно широким диапазоном допустимых значений, независимую от установленной на сервере временной зоны. Их использование определенно имеет практический смысл. Но если требуется хранить даты исторических событий, уходящие в прошлое за Нашу эру, придется выбрать другие типы данных. Для хранения дат неких событий, потенциально выходящих за рамки диапазона типа TIMESTAMP (дни рождений, даты выпуска продуктов, избрания президентов, запуски космических ракет и т.д.), отлично подойдут эти типы. При использовании этих типов нужно учитывать один важный нюанс, но об этом ниже.

Тип TIME можно использовать для хранения промежутка времени, когда не нужна точность меньше 1 секунды, и промежутки времени меньше 829 часов. Добавить тут больше нечего.

Остался самый интересный тип - TIMESTAMP . Рассматривать его надо в сравнении с DATE и DATETIME: TIMESTAMP тоже предназначен для хранения даты и/или времени происхождения неких событий. Важное отличие между ними в диапазонах значений: очевидно, что TIMESTAMP не годится для хранения исторических событий (даже таких, как дни рождений), но отлично подходит для хранения текущих (логирование, даты размещения статей, добавления товаров, оформления заказов) и предстоящих в обозримом будущем событий (выходы новых версий, календари и планировщики и т.д).

Основное удобство использования типа TIMESTAMP состоит в том, что для столбцов этого типа в таблицах можно задавать значение по умолчанию в виде подстановки текущего времени, а так же установки текущего времени при обновлении записи. Если вам требуется эти возможности, то с вероятностью 99% TIMESTAMP - именно то, что вам нужно. (Как этоделать, смотрите в мануале.)

Не стоит бояться того, что с приближением к 2038 году ваш софт перестанет работать. Во-первых, до этого времени вашим софтом, скорее всего, просто перестанут пользоваться (особенно версиями, которые пишутся сейчас). Во-вторых, с приближением к этой дате разработчики MySQL обязательно что-нибудь придумают для сохранения работоспособности вашего софта. Все решится так же хорошо, как проблема Y2K.

Итак, тип TIMESTAMP используем для хранения дат и времени свершения событий нашего времени, а DATETIME и DATE - для хранения дат и времени свершения исторических событий, или событий глубокого будущего.

Диапазоны значений - это важное отличие между типами TIMESTAMP, DATETIME и DATE, но не главное. Главное то, что TIMESTAMP хранит значение в UTC . При сохранении значения оно переводится из текущего временной зоны в UTC, а при его чтении - во время текущей временной зоны из UTC. DATETIME и DATE хранят и выводят всегда одно и то же время, независимо от временных зон.

Временные зоны устанавливаются в СУБД MySQL глобально или для текущего подключения .Последнее можно использовать для обеспечения работы разных пользователей в разных временных зонах на уровне СУБД . Все значения времени физически будут храниться в UTC, а приниматься от клиента и отдаваться клинту - в значениях его временной зоны. Но только при использовании типа данных TIMESTAMP. DATE и DATETIME всегда принимают, хранят и отдают одно и то же значение.

Функция NOW() и ее синонимы возвращают значение времени в текущей временной зоне пользователя.

Учитывая все эти обстоятельства, необходимо быть крайне внимательными при изменении временной зоны в пределах подключения к серверу и использовании типов DATE и DATETIME. Если надо хранить дату (например, дату рождения), то никаких проблем не будет. Дата рождения в любой зоне одинаковая. Т.е. если вы родились 1 января в 0:00 UTC/GMT+0, то это не значит, что в Америке будут праздновать ваш день рождения 31 декабря. Но если вы решите хранить время события в столбце DATETIME, то тут уже построить работу с пользовательскими временными зонами на уровне СУБД просто не выйдет. Поясню на примере:

Пользователь X работает в зоне UTC/GMT+2, Y - в зоне UTC/GMT+3. Для соединений пользователей с MySQL установлена соответствующая (у каждого своя) временная зона. Пользователь размещает сообщение на форуме, нас интересует дата написания сообщения.

Вариант 1: DATETIME. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. Значение в поле «дата» сообщения подставляется как результат выполнения функции NOW() - 14:00. Пользователь Y считывает время написания сообщения и видит те же 14:00. Но у него в настройках стоитзона UTC/GMT+3, и он думает, что сообщение было написано не только что, а час назад.

Вариант 2: TIMESTAMP. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. В поле «дата» попадает результат выполнения функции NOW() - в данном случае - 12:00 UTC/GMT+0. ПользовательY считывает время написания сообщения и получает (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Все получается ровно так, как мы хотим. И главное - пользоваться этим крайне удобно: для поддержки пользовательских временных зон не нужно писать никакой код приведения времени.

Возможности подстановки текущего времени и работы с временными зонами в типе TIMESTAMP настолько весомы, что если вам в неком логе надо хранить дату без времени, все равно стоит использовать TIMESTAMP, вместо DATE, не экономя 1 байт разницы между ними. При этом на «00:00:00» просто не обращать внимания.

Если же вы не можете использовать TIMESTAMP из-за относительно малого диапазона его значений (а обычно это 1-2 случая против 10-15 в базе сайта), придется использовать DATETIME и аккуратно его корректировать значения в нужных местах (т.е. при записи в это поле переводить дату в UTC, а при чтении - во время в зоне считывающего пользователя). Если вы храните только дату, то скорее всего не важно, какая у вас временная зона: новый год все празднуют 1 января по локальному времени, ничего переводить тут не понадобится.