Денис Бирюков

Работа с датами в MySQL.

29.06.2014 MySQL

Для начала, хочу затронуть тему о том, в каком формате лучше хранить даты в базе: 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` < CURDATE();
// Записи за текущую неделю
SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND WEEK(`date_field`) = WEEK(NOW());
// И за текущий месяц
SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = MONTH(NOW());
// Выбрать из базы записи за последние 30 дней
SELECT * FROM `table_name` WHERE `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 для работы с датами в этой статье, поможет вам сориентировать в ситуации и принять правильное решение. Если всё-таки возникнут сложности, то задавайте вопросы в этой теме или разделе "Ваш вопрос". Будем разбираться вместе ;)