Mysql функции работы с датой и временем. Календарные типы данных в 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.
Вот список определителей этой функции

Определитель Описание
%M Название месяца (январь...декабрь)
%W Название дня недели (воскресенье...суббота)
%D День месяца с английским суффиксом (0st, 1st, 2nd, 3rd и т.д.)
%Y Год, число, 4 разряда
%y Год, число, 2 разряда
%X Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%V"
%x Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%v"
%a Сокращенное наименование дня недели (Вс...Сб)
%d День месяца, число (00..31)
%e День месяца, число (0..31)
%m Месяц, число (00..12)
%c Месяц, число (0..12)
%b Сокращенное наименование месяца (Янв...Дек)
%j День года (001..366)
%H Час (00..23)
%k Час (0..23)
%h Час (01..12)
%I Час (01..12)
%l Час (1..12)
%i Минуты, число (00..59)
%r Время, 12-часовой формат (hh:mm:ss M)
%T Время, 24-часовой формат (hh:mm:ss)
%S Секунды (00..59)
%s Секунды (00..59)
%p AM или PM
%w День недели (0=воскресенье..6=суббота)
%U Неделя (00..53), где воскресенье считается первым днем недели
%u Неделя (00..53), где понедельник считается первым днем недели
%V Неделя (01..53), где воскресенье считается первым днем недели. Используется с `%X"
%v Неделя (01..53), где понедельник считается первым днем недели. Используется с `%x"
%% Литерал `%".

Комментарии

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 функции и она конечно имеет право на существование...

Эти функции также предназначены для работы с календарными типами данных. Рассмотрим их подробнее.

  • DATE_FORMAT(date, format) форматирует дату date в соответствии с выбранным форматом formate. Эта функция очень часто используется. Например, в MySQL дата имеет формат представления YYYY-MM-DD (год-месяц-число), а нам привычнее формат DD-MM-YYYY (число-месяц-год). Поэтому для привычного нам отображения даты ее необходимо переформатировать. Давайте сначала приведем запрос, а затем разберемся, как задавать формат:

    SELECT DATE_FORMAT(CURDATE(), "%d.%m.%Y");

    Теперь дата выглядит для нас привычно. Для задания формата даты используются специальные определители. Для удобства перечислим их в таблице.

    Опред Описание
    %a Сокращенное наименование дня недели (Mon - понедельник, Tue - вторник, Wed - среда, Thu - четверг, Fri - пятница, Sat - суббота, Sun - воскресенье).

    Пример:

    SELECT DATE_FORMAT(CURDATE(), "%a");

    Результат:

    %b Сокращенное наименование месяцев (Jan - январь, Feb - февраль, Mar - март, Apr - апрель, May - май, Jun - июнь, Jul - июль, Aug - август, Sep - сентябрь, Oct - октябрь, Nov - ноябрь, Dec - декабрь).

    Пример:

    SELECT DATE_FORMAT(CURDATE(), "%b");

    Результат:

    %c Месяц в числовой форме (1 - 12).

    Пример:

    SELECT DATE_FORMAT(CURDATE(), "%с");

    Результат:

    %d День месяца в числовой форме с нулем (01 - 31).

    Пример:

    SELECT DATE_FORMAT(CURDATE(), "%d");

    Результат:

    %D День месяца в английском варианте (1st, 2nd...).

    Пример:

    SELECT DATE_FORMAT(CURDATE(), "%D");

    Результат:

    %e День месяца в числовой форме без нуля (1 - 31).

    Пример:

    SELECT DATE_FORMAT(CURDATE(), "%e");

    Результат:

    %H Часы с ведущим нулем от 00 до 23.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 23:03:20", "%H");

    Результат:

    %h Часы с ведущим нулем от 00 до 12.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 23:03:20", "%h");

    Результат:

    %i Минуты от 00 до 59.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 23:03:20", "%i");

    Результат:

    %j День года от 001 до 366.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 23:03:20", "%j");

    Результат:

    %k Часы c ведущим нулем от 0 до 23.

    Пример:

    SELECT DATE_FORMAT("2011-12-31 01:03:20", "%k");

    Результат:

    %l Часы без ведущим нуля от 1 до 12.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 00:03:20", "%l");

    Результат:

    %M

    Пример:

    Результат:

    %M Название месяца без сокращения.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 00:03:20", "%M");

    Результат:

    %m Месяц в числовой форме с ведущим нулем (01 - 12).

    Пример:

    SELECT DATE_FORMAT("2011-04-15 00:03:20", "%m");

    Результат:

    %p АМ или РМ для 12-часового формата.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 00:03:20", "%p");

    Результат:

    %r Время в 12-часовом формате.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 00:03:20", "%r");

    Результат:

    %s Секунды от 00 до 59.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 00:03:20", "%s");

    Результат:

    %T Время в 24-часовом формате.

    Пример:

    SELECT DATE_FORMAT("2011-04-15 21:03:20", "%T");

    Результат:

    %u Неделя (00 - 52), где первым днем недели считается понедельник.

    Пример:

    SELECT DATE_FORMAT("2011-04-17 21:03:20", "%u");

    Результат:

    %U Неделя (00 - 52), где первым днем недели считается воскресенье.

    Пример:

    SELECT DATE_FORMAT("2011-04-17 21:03:20", "%U");

    Результат:

    %W Название дня недели без сокращения.

    Пример:

    SELECT DATE_FORMAT("2011-04-17 21:03:20", "%W");

    Результат:

    %w Номер дня недели (0 - воскресенье, 6 - суббота).

    Пример:

    SELECT DATE_FORMAT("2011-04-17 21:03:20", "%w");

    Результат:

    %Y Год, 4 разряда.

    Пример:

    SELECT DATE_FORMAT("2011-04-17 21:03:20", "%Y");

    Результат:

    %y Год, 2 разряда.

    Пример:

    SELECT DATE_FORMAT("2011-04-17 21:03:20", "%y");

    Результат:


  • STR_TO_DATE(date, format) функция обратная предыдущей, она принимает дату date в формате format, а возвращает дату в формате MySQL.

    SELECT STR_TO_DATE("17.04.2011 23:50", "%d.%m.%Y %H:%i");



  • TIME_FORMAT(time, format) функция аналогична функции DATE_FORMAT(), но используется только для времени:

    SELECT TIME_FORMAT("22:38:15", "%H-%i-%s");



  • GET_FORMAT(date, format) функция возвращает строку форматирования, соответствующую одному из пяти форматов времени:

    EUR - европейский стандарт
    USA - американский стандарт
    JIS - японский индустриальный стандарт
    ISO - стандарт ISO (международная организация стандартов)
    INTERNAL - интернациональный стандарт

    Эту функцию хорошо использовать совместно с предыдущей - DATE_FORMAT(). Посмотрим на примере:

    SELECT GET_FORMAT(DATE, "EUR"), DATE_FORMAT("2011-04-17", GET_FORMAT(DATE, "EUR"));


    Как видите, сама функция GET_FORMAT() возвращает формат представления, а вместе с функцией DATE_FORMAT() выдает дату в нужном формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.

Ну вот, теперь вы знаете о работе с датами и временем в MySQL практически все. Это вам очень пригодится при разработке различных web-приложений. Например, если пользователь в форму на сайте вводит дату в привычном ему формате, вам не составит труда применить нужную функцию, чтобы в БД дата попала в нужном формате.

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

    Как строка в формате "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" .

Итак, все календарные типы данных подробно описаны в разделе «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 байт.
Хозяйке на заметку . Интересно то, что большинство программистов полагают, что понятие «timestamp» - это и есть Unix-время. На самом же деле, timestamp - это метка, которая представляет собой последовательность символов, обозначающих дату и / или время, когда определенное событие произошло. А «время Юникса » (Unix time) или POSIX time - это количество секунд, прошедших с полуночи 1 января 1970 года по UTC. Понятие timestamp шире, чем Unix time.

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

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

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

Начнем с самого простого - тип YEAR . Единственное его достоинство - малый размер - всего-то 1 байт. Но из-за этого действует строгое ограничение по диапазону допустимых значений (тип может хранить только 255 разных значений). Мне сложно представить практическую ситуацию, когда может потребоваться хранить года строго в диапазоне от 1901 до 2155. Кроме того, тип SMALLINT (2 байта) дает диапазон, достаточный в большинстве ситуаций для хранения года. А экономить 1 байт на строке в таблице БД в наше время смысла нет.

Типы 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 января по локальному времени, ничего переводить тут не понадобится.

Для начала, хочу затронуть тему о том, в каком формате лучше хранить даты в базе: 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 для работы с датами в этой статье, поможет вам сориентировать в ситуации и принять правильное решение. Если всё-таки возникнут сложности, то задавайте вопросы в этой теме или разделе "Ваш вопрос ". Будем разбираться вместе;)



СХОЖИЕ СТАТЬИ