Преобразование даты в mysql с помощью DATE_FORMAT()
Как известно, все даты хранятся в 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 [AP]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[date]»));
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 функции и она конечно имеет право на существование.
Выборка c условием по дате в mysql БД через PHP
Одной из самой сложной работы в php считаю запросы в mysql по дате. Во-первых, не всегда логически просто понять что нужно выбрать если у нас сложные условия, типа: выбрать все даты из БД, которые больше такого-то отрезка времени, но меньше текущей даты увеличенной на 25 дней. Во-вторых, возникают другие проблемы от формата до самих запросов.
Начнем с формата хранения даты в БД
Вспомните тип поля timestamp, у него формат времени такой: гггг-мм-дд чч:мм:сс, например: 2017-11-05 14:23:05. Рекомендую использовать в своих базах именно этот формат, т.к. это стандарт. За счет него можно будет выгружать только время или дату, или одновременно то и другое. Не обязательно тип поля брать timestamp, можно использовать обычный text.
Примеры mysql запросов
Теперь рассмотрим практические примеры выборок по дате с учетом того, что дата записана в формате гггг-мм-дд чч:мм:сс:
1. Выбираем все записи в диапазоне: дата в БД < текущей
select * from ruefz_jcomments where DATE(date)
2. Выбираем данные за текущий месяц: дата в БД > от 1 числа этого месяца и дата < 1 числа следующего месяца
select id from tab where date > LAST_DAY(CURDATE()) + INTERVAL 1 DAY — INTERVAL 1 MONTH and date < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)
Расшифровка: выбрать все id записи, в которых дата > последний день месяца сегодняшней даты + 1 день — 1 месяц и дата < последний день месяца сегодняшней даты + 1 день
3. Выбираем данные за текущий месяц, год и день не учитывается (%c месяц 1-12, %e дни 1-31 в mysql; n — месяц 1-12, j дни 1-31 в PHP )
select id from tab where DATE_FORMAT(date, ‘%c’)='».date(‘n’).»‘
4. За текущую неделю
select id from tab where date > DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY) and date < DATE_ADD(CURDATE(), INTERVAL (9 - DAYOFWEEK(CURDATE())) DAY)
5. выбираем все записи в диапазоне: (текущая — 10 дней) < дата в БД < текущая дата
select * from ruefz_jcomments where DATE(date) = DATE_SUB(NOW(), INTERVAL 10 DAY)
По этим примерам вам будет понятно, как составлять свои решения. Зачастую, при работе с датами вам потребуется использовать запросы DATE_ADD – прибавление, DATE_SUB — вычитание, CURDATE() — сегодняшняя дата, NOW() — сегодняшняя дата и время.
Изменить формат вывода времени в mysql
Если из БД надо взять дату в другом формате, мы можем изменить ее на «лету», для обработки на php скриптах:
DATE_FORMAT(date,format) — Форматирует величину date в соответствии со строкой format.
Пример:
$result = mysql_query(«SELECT DATE_FORMAT(‘1997-10-04 22:23:01’, ‘%d.%m.%Y %H:%i:%S’) AS datess FROM csv»);
while ($row=mysql_fetch_array($result)) echo $row[«datess»].»
«; >//05.11.2017 14:23:55
В последнем примере мы приводим дату из БД в читаемый вид для пользователя.
Только дата sql php
Функция EXTRACT извлекает из даты и времени какой-то определенный компонент. Ее формальный синтаксис:
EXTRACT(unit FROM datetime)
Значение datetime представляет исходную дату и (или) время, а значение unit указывает, какой компонент даты или времени будет извлекаться. Параметр unit может представлять одно из следующих значений:
- SECOND (секунды)
- MINUTE (минуты)
- HOUR (час)
- DAY (день)
- MONTH (месяц)
- YEAR (год)
- MINUTE_SECOND (минуты и секунды)
- HOUR_MINUTE (часы и минуты)
- DAY_HOUR (день и часы)
- YEAR_MONTH (год и месяц)
- HOUR_SECOND (часы, минуты и секунды)
- DAY_MINUTE (день, часы и минуты)
- DAY_SECOND (день, часы, минуты и секунды)
EXTRACT( SECOND FROM ‘2018-05-25 21:25:54’)
EXTRACT( MINUTE FROM ‘2018-05-25 21:25:54’)
EXTRACT( HOUR FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY FROM ‘2018-05-25 21:25:54’)
EXTRACT( MONTH FROM ‘2018-05-25 21:25:54’)
EXTRACT( YEAR FROM ‘2018-05-25 21:25:54’)
EXTRACT( MINUTE_SECOND FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY_HOUR FROM ‘2018-05-25 21:25:54’)
EXTRACT( YEAR_MONTH FROM ‘2018-05-25 21:25:54’)
EXTRACT( HOUR_SECOND FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY_MINUTE FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY_SECOND FROM ‘2018-05-25 21:25:54’)
Функции для манипуляции с датами
Ряд функций позволяют производить операции сложения и вычитания с датами и временем:
- DATE_ADD(date, INTERVAL expression unit) возвращает объект DATE или DATETIME, который является результатом сложения даты date с определенным временным интервалом. Интервал задается с помощью выражения INTERVAL expression unit , где INTERVAL предоставляет ключевое слово, expression — количество добавляемых к дате единиц, а unit — тип единиц (часы, дни и т.д.) Параметр unit может иметь те же значения, что и в функции EXTRACT, то есть DAY, HOUR и т.д.
- DATE_SUB(date, INTERVAL expression unit) возвращает объект DATE или DATETIME, который является результатом вычитания из даты date определенного временного интервала
- DATEDIFF(date1, date2) возвращает разницу в днях между датами date1 и date2
- TO_DAYS(date) возвращает количество дней с 0-го года
- TIME_TO_SEC(time) возвращает количество секунд, прошедших с момента полуночи
DATE_ADD(‘2018-05-25’, INTERVAL 1 DAY)
DATE_ADD(‘2018-05-25’, INTERVAL 3 MONTH)
DATE_ADD(‘2018-05-25 21:31:27’, INTERVAL 4 HOUR)
DATE_SUB(‘2018-05-25’, INTERVAL 4 DAY)
Форматирование дат и времени
- DATE_FORMAT(date, format) возвращает объект DATE или DATETIME, отформатированный с помощью шаблона format
- TIME_FORMAT(date, format) возвращает объект TIME или DATETIME, отформатированный с помощью шаблона format
Обе функции в качестве второго параметра принимают строку форматирования или шаблон, который показывает, как оформатировать значение. Этот шаблон может принимать следующие значения:
- %m : месяц в числовом формате 01..12
- %с : месяц в числовом формате 1..12
- %M : название месяца (January. December)
- %b : аббревиатура месяца (Jan. Dec)
- %d : день месяца в числовом формате 00..31
- %e : день месяца в числовом формате 0..31
- %D : номер дня месяца с суффиксом (1st, 2nd, 3rd. )
- %y : год в виде двух чисел
- %Y : год в виде четырех чисел
- %W : название дня недели (Sunday. Saturday)
- %a : аббревиатура дня недели (Sun. Sat)
- %H : час в формате 00..23
- %k : час в формате 0..23
- %h : час в формате 01..12
- %l : час в формате 1..12
- %i : минуты в формате 00..59
- %r : время в 12-ти часовом формате (hh:mm:ss AM или PM)
- %T : время в 24-ти часовом формате (hh:mm:ss)
- %S : секунды в формате 00..59
- %p : AM или PM
DATE_FORMAT(‘2018-05-25 21:25:54’, ‘%d %M %Y’)
DATE_FORMAT(‘2018-05-25 21:25:54’, ‘%r’)
TIME_FORMAT(‘2018-05-25 21:25:54’, ‘%H:%i:%S’)
В качестве примера использования функций найдем заказы, которые были сделаны 5 дней назад:
SELECT * FROM Orders WHERE DATEDIFF(CURDATE(), CreatedAt) = 5;