Веб-инструмент
Веб-мастерская
Группа функция даты и времени предназначена для форматирования и преобразования календарных значений. В таблице представлен список календарных функций, доступных в СУБД MySQL.
Функции даты и времени MySQL
| Функции | Описание |
| ADDDATE(date, INTERVAL expr type), DATE_ADD(date, INTERVAL expr type) |
Возвращает время date, к которому прибавлен временной интервал, определяемый вторым параметром |
| ADDTIME(expr1, expr2) |
Возвращает результат сложения двух календарных значений expr1 и expr2 |
| CONVERT_TZ(dt, from_tz, to_tz) |
Переводит DATETIME-значение dt из часового пояса from_tz в to_tz. Если аргументы ошибочны возвращает NULL |
| CURDATE(), CURRENT_DATE, CURRENT_DATE() |
Возвращает текущую дату в формате 'YYYY-MM-DD' или YYYYDDMM в зависимости от того, вызывается функция в текстовом или числовом контексте |
| CURTIME(), CURRENT_TIME, CURRENT_TIME() |
Возвращает текущее время суток в виде 'hh-mm-ss' или 'hhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте |
| DATE(datetime) |
Извлекает из значения даты и времени суток в формате DATETIME(например '2009-03-18 00:26:23') дату,отсекая часы, минуты и секунды — '2009-03-18' |
| DATEDIFF(begin, end) |
Вычисляют разницу в днях между датами begin и end. Аргументы функции могут иметь тип DATE и DATETIME, однако при вычислении разницы используется только DATE-часть |
| DATE_FORMAT(date, format) |
Форматирует время date в соответствии со строкой format |
| DAY(date), DAYOFMONTH(date) |
Принимает в качестве аргумента дату date и возвращает порядковый номер дня в месяце (от 1 до 31) |
| DAYNAME(date) |
Принимает в качестве аргумента дату date и возвращает день недели в виде полного английского названия |
| DAYOFWEEK(date) |
Принимает в качестве аргумента дату date и возвращает порядковый номер дня недели. Следует помнить, что в западных странах неделя начинается с воскресенья, для которого функция возвращает 1, для последнего дня недели, субботы, возвращается 7 |
| DAYOFYEAR(date) |
Принимает в качестве аргумента дату date и возвращает порядковый номер дня в году (от 1 до 366) |
| EXTRACT(type FROM datetime) |
Принимает дату и время суток datetime и возвращает часть, определяемую параметром type |
| FROM_DAYS(N) |
Принимает количество дней N, прошедших с нулевого года, и возвращает дату в формате 'YYYY-MM-DD' |
| FROM_UNIXTIME(unix_timestamp), FROM(unix_timestamp, format) |
Принимает количество секунд, прошедших с полуночи 1 января 1970 года, и возвращает дату и время суток в виде строки 'YYYY-MM-DD hh-mm-ss' или в виде числа YYYYMMDDhhmmss в зависимости от того, вызвана функция в строковом или числовом контексте |
| GET_FORMAT(date|time|datetime, 'EUR'|'USA'|'JIS'|'ISO'|'INTERVAL') | Возвращает строку форматрования, соответствующую одному из пяти стандартов времени |
| HOUR(datetime) | Возвращает значение часа (от 0 до 23) для времени datetime |
| LAST_DAY(datetime) |
Принимает в качестве параметра значение даты datetime в кратком 'YYYY-MM-DD' или расширенном формате 'YYYY-MM-DD hh:mm:ss' и возвращает дату в кратком формате 'YYYY-MM-DD', день в котором выставлен на последний день текущего месяца |
| MAKEDATE(year, dayofyear) |
Принимает в качестве параметров год year, номер дня в году dayofyear и возвращает дату в формате 'YYYY-MM-DD' |
| MAKETIME(hour, minute, second) |
Принимает три параметра: часы hour, минуты minute и секунды second. В качестве результата функция возвращает время суток в формате 'hh:mm:ss' |
| MICROSECOND(datetime) | Извлекает из временного значения datetime микросекунды |
| MINUTE(datetime) | Возвращает значение минут (от 0 до 59) для времени суток datetime |
| MONTH(datetime) | Возвращает числовое значение месяца года (от1 до 12) для даты datetime |
| MONTHNAME(datetime) | Возвращает строку с названием месяца для даты datetime |
| NOW(), CURRENT_TIME, CURRENT_TIME(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP, SYSDATE() |
Возвращает текущие дату и время в виде строки в формате 'YYYY-MM-DD hh:mm:ss' или в виде числа YYYYMMDDhhmmss, в зависимости от того, вызывается функция в строковом или числовом контексте |
| PERIOD_ADD(period, N) |
Добавляет N месяцев к значению даты period. Аргумент period длжен быть представлен в чмсловом формате YYYYMM или YYMM. Передача в качестве аргумента даты в любом другом формате приводит к непредсказуемому результату. |
| PERIOD_DIFF(period1, period2) |
Вычисляет разницу в месяцах между двумя датами period1 и period2, которые представлены в числовом формате YYYYMM или YYMM |
| QUARTER(datetime) |
Возвращает значение квартала года (от 1 до 4) для даты datetime, которая передается в формате 'YYYY-MM-DD' или 'YYYY-MM-DD hh:mm:ss' |
| SECOND(time) |
Возвращает количество секунд для времени суток time, которая задается либо в виде строки 'hh:mm:ss', либо числа hhmmss |
| SEC_TO_TIME(seconds) |
Принимает количество секунд seconds, прошедших от начала суток, и возвращает время в формате 'hh:mm:ss' или hhmmss, в зависимости от того, вызывается функция в строковом или числовом контексте |
| STR_TO_DATE(str, format) | Принимает дату и время суток в виде строки str, соответствующей формату format, и возвращает время в формате MySQL 'YYYY-MM-DD hh:mm:ss' |
| SUBDATE(date, INTERVAL expr interval), DATE_SUB(date, INTERVAL expr interval) |
Возвращает дату date, из которой вычисляется временной интервал, определяемый вторым праметром |
| SUBTIME(datetime, time) |
Вычитает из временной величины datetime время суток time и возвращает результат |
| TIME(datetime) | Извлекает время суток 'hh:mm:ss' из даты и времени datetime |
| TIMEDIFF(expr1, expr2) |
Возвращает разницу между временными значениями, заданными параметрами expr1 и expr2 |
| TIMESTAMP(expr) |
Принимает в качестве аргумента дату и время суток в полном или кратком форматах и возвращает полный вариант в формате 'YYYY-MM-DD hh:mm:ss' |
| TIMESTAMPADD(interval, int_expr, datetime_expr) |
Прибавляет к дате и времени суток datetime_expr в полном или кратком формате временной интервал int_expr, единицы измерения которого задаются параметром interval |
| TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2) |
Возвращают разницу между двумя датами datetime_expr1 или datetime_expr2, заданных в кратком 'YYYY-MM-DD' или полном 'YYYY-MM-DD hh:mm:ss' форматах. Единицы измерения, в которых функция возвращает результат, задаются параметром interval |
| TIME_FORMAT(time, format) |
Форматирует время time, которое задается в виде 'hh:mm:ss' согласно строке форматирования format |
| TIME_TO_SEC(time) |
Принимает в качестве аргумента время суток time в формате 'hh:mm:ss' и возвращает количество секунд, прошедших с начала суток |
| TO_DAYS(date) |
Принимает дату date в полном 'YYYY-MM-DD hh:mm:ss' или кратком 'YYYY-MM-DD' формате и возвращает количество дней, прошедших с нулевого года |
| UNIX_TIMESTAMP(), UNIX_TIMESTAMP(datetime) |
Вариант без параметра datetime возвращает количество секунд, прошедших с полуночи 1 января 1970 года. Кроме этого, функция может принимать необязательный параметр datetime, определяющий дату в кратком 'YYYY-MM-DD' или полном 'YYYY-MM-DD hh:mm:ss' форматах, возвращая в этом случае разницу в секундах между 1 января 1970 года и указанной datetime датой |
| UTC_DATE() |
Возвращает текущую дату в виде строки 'YYYY-MM-DD' или числа YYYYMMDD в зависимости от того, в каком контексте вызывается функция. В отличии от функции NOW() или CURDATE(), возвращающих локальную дату, в UTC_DATE() время исчисляемое по Гринвичу |
| UTC_TIME() |
Возвращает текущее время суток в виде строки 'hh:mm:ss' или числа hhmmss в зависимости от того, в каком контексте вызывается функция NOW() или CURTIME(), возвращающих локальную дату, в UTC_DATE() время исчисляется по Гринвичу |
| UTC_TIMESTAMP() |
Возвращает дату и время суток в виде строки 'YYYY-MM-DD hh:mm:ss' или в виде числа YYYYMMDDhhmmss в зависимости от того, в каком контексте вызывается функция. В отличии от функции NOW(), возвращающей локальную дату, в UTC_DATE() время исчисляется по Гринвичу |
| WEEK(date) | Возвращает номер недели (от 0 до 53) в году для даты date |
| WEEKDAY(date) |
Принимает дату date в полном 'YYYY-MM-DD hh:mm:ss' или кратком 'YYYY-MM-DD' форматах и возвращает номер дня недели (0 для понедельника, 1 для вторника, . . ., 6 для воскресенья) |
| WEEKOFYEAR(datetime) |
Возвращает порядковый номер недели в году (от 1 до 53) для даты datetime |
| YEAR(datetime) | Возвращает год (от 1000 до 9999) для даты datetime |
| YEARWEEK(date) |
Возвращает число в формате YYYYWW, представляющее год и номер недели (от 0 до 53) в году, соответствующие дате date. Предполагается, что неделя начинается с воскресенья(как это принято на Западе) |
Функция DATE_FORMAT(date, format) форматирует время date в соответствии со строкой format. Встроке формат могут использоваться определители, представленные в таблице.
Определители строки форматирования функции DATE_FORMAT ()
| Определитель | Описание |
| %a | Cокращенное наименование дня недели (Sun, . . . , Sat) |
| %b | Cокращенное наименование месяца (Jan, . . . , Dec) |
| %c | Месяц в числовой форме (1, . . . , 12) |
| %D | День месяца с английским суффиксом (1st, 2nd, 3rd и т.д.) |
| %d | День месяца в числовой форме с ведущим нулем(01, . . ., 31) |
| %e | День месяца в числовой форме (1, . . ., 31) |
| %f | Микросекунды (000000, . . ., 999999) |
| %H | Час с ведущим нулем (00, . . ., 23) |
| %h | Час с ведущим нулем (01, . . ., 12) |
| %I | Час с ведущим нулем (01, . . ., 12) |
| %i | Минуты с ведущим нулем (00, . . ., 59) |
| %j | День года(001, . . ., 366) |
| %k | Час с ведущим нулем (0, . . ., 23) |
| %l | Час без ведущего нуля (1, . . ., 12) |
| %M | Название месяца (January, . . ., December) |
| %m | Месяц в числовом форме с ведущим нулем (01, . . ., 12) |
| %p | AM или PM(для 12-часового формата) |
| %r | Время, 12-часовой формат (hh:mm:ss AM или hh:mm:ss PM) |
| %S | Секунды (00, . . ., 59) |
| %s | Секунды (00, . . ., 59) |
| %T | Время, 24-часовой формат (hh:mm:ss) |
| %U | Неделя (00, . . ., 52), где воскресенье считается первым днем недели |
| %u | Неделя (00, . . ., 52), где понедельник считается первым днем недели |
| %V | Неделя (01, . . ., 53), где воскресенье считается первым днем недели. Используется с %Х |
| %v | Неделя (01, . . ., 53), где понедельник считается первым днем недели. Используется с %Х |
| %W | Название дня недели (Sunday, . . . , Satuday) |
| %w | День недели (0, . . ., 6), где 0 — воскресенье, . . ., 6 — суббота |
| %X | Год в который началась неделя, где воскресенье считается первым днем недели, 4 разряда, используется с %V |
| %x | Год в который началась неделя, где воскресенье считается первым днем недели, 4 разряда, используется с %v |
| %Y | Год, 4 разряда (YYYY) |
| %y | Год, 2 разряда (YY) |
| %% | Литерал % |
Все другие символы, которые не указаны в таблице, выводятся без изменений.
Рассмотрим наиболее часто встречающуюся задачу преобразования календарного значения из MySQL-формата '2009-03-20 11:53:25' в привычный формат '20.03.2009 11:53'.
Преобразование даты при помощи функции DATE_FORMAT()
Создадим таблицу tb4, содержащую сведения о сотрудниках и состоящую из трех столбцов:
Создание таблицы tb4
Вставка данных в таблицу tb4
Необходимо вычислить возраст сотрудников на текущую дату. Один из вариантов состоит в преобразовании даты рождения и текущей даты в дни при помощи функции TO_DAYS() делению на число 365.25: в году 365 дней; дробное число 0,25 призвано компенсировать високосные года, которые случаются раз в четыре года.
Вычисление возраста сотрудников
Для того, чтобы избавиться от дробной части, можно воспользоваться функцией FLOOR().
Отбрасывание дробной части
Пожалуй, самым распространенным является формат хранения времени UNIXSTAMP. Его популярность обусловлена удобством операций сложения и вычитания временных интервалов. UNIXSTAMP-формат — это количество секунд, прошедших с полуночи 1 января 1970 года. Для преобразования MySQL-даты в UNIXSTAMP-формат используется функция UNIX_TIMESTAMP([datetime]). Функция может принимать необязательный параметр datetime, определяющий дату в кратком 'YYYY-MM-DD' или полном 'YYYY-MM-DD hh:mm:ss' форматах, возвращая в этом случае разницу в секундах между 1 января 1970 года и указанной в datetime датой.
Использование функции UNIX_TIMESTAMP()
Функция FROM_UNIXTIME(unix_timestamp) решает обратную задачу: принимает число секунд, прошедших с полуночи 1 января 1970 года, и возвращает дату и время суток в виде строки 'YYYY-MM-DD hh:mm:ss' или в виде числа YYYYMMDDhhmmss в зависимости от того, вызвана функция в строковом или числовом контексте.
Использование функции FROM_UNIXTIME()
Как видно из примера, передача функции значения 0 в качестве аргумента приводит к выводу вместо полуночи 3 часа ночи. Это связано с тем, что время на машине, где расположен сервер MySQL, настроено для третьей временной зоны.
Функция FROM_UNIXTIME(unix_timestamp, format) может принимать втрой параметр format, который представляет собой строку, содержащую определители. Использование определителей позволяет отформатировать строку.
Форматирование результатов функции FROM_UNIXTIME()
Обсудить на форуме