ресурс для начинающих веб-разработчиков
комплексные веб-услуги по созданию сайтов

Справочный материал по основным языкам программирования и верстки сайтов.

Готовая методика создания простых и сложных динамичных сайтов, с использованием PHP и MySQL.

Использование веб-редактора Adobe Dreamweaver в разработке сайтов.

Использование графических редакторов Adobe Flash, Adobe Photoshop, Adobe Fireworks в подготовке веб-графики.

Разработка веб-сайтов под "ключ".

Разработка отдельных фрагментов сайтов, консультации по вопросам верстки веб-страниц и веб-программирования.

Функции даты и времени MySQL

Функции даты и времени

Функции даты и времени MySQL

Группа функция даты и времени предназначена для форматирования и преобразования календарных значений. В таблице представлен список календарных функций, доступных в СУБД 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()

Преобразование даты при помощи функции DATE_FORMAT()

Преобразование даты при помощи функции DATE_FORMAT()

Вычисление возраста человека

Создадим таблицу tb4, содержащую сведения о сотрудниках и состоящую из трех столбцов:

  • id — первичный ключ таблицы;
  • fio — фамилия, имя, отчество сотрудника;
  • putdate — дата рождения.
Создание таблицы tb4

Создание таблицы tb4

Вставка данных в таблицу 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()

Использование функции UNIX_TIMESTAMP()

Функция FROM_UNIXTIME(unix_timestamp) решает обратную задачу: принимает число секунд, прошедших с полуночи 1 января 1970 года, и возвращает дату и время суток в виде строки 'YYYY-MM-DD hh:mm:ss' или в виде числа YYYYMMDDhhmmss в зависимости от того, вызвана функция в строковом или числовом контексте.

Использование функции FROM_UNIXTIME()

Использование функции FROM_UNIXTIME()

Как видно из примера, передача функции значения 0 в качестве аргумента приводит к выводу вместо полуночи 3 часа ночи. Это связано с тем, что время на машине, где расположен сервер MySQL, настроено для третьей временной зоны.

Функция FROM_UNIXTIME(unix_timestamp, format) может принимать втрой параметр format, который представляет собой строку, содержащую определители. Использование определителей позволяет отформатировать строку.

Форматирование результатов функции FROM_UNIXTIME()

Форматирование результатов функции FROM_UNIXTIME()