Функции даты и времени 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. В строке формат могут использоваться определители, представленные в таблице.
| Определитель | Описание |
|---|---|
| %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, содержащую сведения о сотрудниках и состоящую из трех столбцов:
id— первичный ключ таблицы;fio— фамилия, имя, отчество сотрудника;putdate— дата рождения.
Создание таблицы tb4
Вставка данных в таблицу tb4
Необходимо вычислить возраст сотрудников на текущую дату. Один из вариантов состоит в преобразовании даты рождения и текущей даты в дни при помощи функции TO_DAYS() делению на число 365.25: в году 365 дней; дробное число 0,25 призвано компенсировать високосные года, которые случаются раз в четыре года.
Вычисление возраста сотрудников
Для того, чтобы избавиться от дробной части, можно воспользоваться функцией FLOOR().
Отбрасывание дробной части
Преобразование даты в UNIXSTAMP- формат
Пожалуй, самым распространенным является формат хранения времени 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()
Комментарии(0)
Для добавления комментариев надо войти в систему и авторизоватьсяКомментирование статей доступно только для зарегистрированных пользователей:Зарегистрироваться