Работа с датой и временем в MySQL

В данной статье мы рассмотрим основы работы с датой и временем в MySQL.

Формат даты и времени

MySQL date format поддерживает пару форматов даты и времени. Их можно определить следующим образом:

DATE — хранит значение даты в виде ГГГГ-ММ-ДД. К примеру, 2008-10-23. DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59 TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.

Создание полей ввода даты и времени

Таблица, содержащая типы данных DATE и DATETIME, создается так же, как и иные столбцы. К примеру, мы можем создать новую таблицу под названием orders, которая включает столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:

CREATE TABLE `MySampleDB`.`orders`( `order_no` INT NOT NULL AUTO_INCREMENT, `order_item` TEXT NOT NULL, `order_date` DATETIME NOT NULL, `order_delivery` DATE NOT NULL, PRIMARY KEY(`order_no`))ENGINE = InnoDB;

Столбец ORDER_DATE — это поле ввода типа MySQL DATE TIME, в которое мы записываем дату и время, когда был сделан заказ. Для даты доставки невозможно предсказать точное время, так что мы записываем только дату.

Форматы даты и времени

Наиболее часто используемым разделителем для дат будет тире( ), а для времени — двоеточие( :). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.

К примеру, все следующие форматы являются правильными:

2008-10-23 10:37:22200810231037222008/10/23 10.37.222008*10*23*10*37*22

Возможности даты и времени

MySQL включает несколько возможностей, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых возможностей:

Функцию Описание
ADDDATE() Добавляет дату.
ADDTIME() Добавляет время.
CONVERT_TZ() Конвертирует из одного часового пояса в другой.
CURDATE() Возвращает текущую дату.
CURTIME() Возвращает текущее системное время.
DATE_ADD() Добавляет одну дату к другой.
MySQL DATE_FORMAT() Задает указанный формат даты.
DATE() Извлекает часть даты из даты или выражения дата-время.
DATEDIFF() Вычитает одну дату из другой.
DAYNAME() Возвращает день недели.
DAYOFMONTH() Возвращает день месяца(1-31).
DAYOFWEEK() Возвращает индекс дня недели из аргумента.
DAYOFYEAR() Возвращает день года(1-366).
EXTRACT Извлекает часть даты.
FROM_DAYS() Преобразует номер дня в дату.
FROM_UNIXTIME() Задает формат даты в формате UNIX.
MySQL DATE_SUB() Вычитает одну дату из другой.
HOUR() Извлекает час.
LAST_DAY Возвращает последний день месяца для аргумента.
MAKEDATE() Создает дату из года и дня года.
MAKETIME() Возвращает значение времени.
MICROSECOND() Возвращает миллисекунды из аргумента.
MINUTE() Возвращает минуты из аргумента.
MONTH() Возвращает месяц из переданной даты.
MONTHNAME() Возвращает название месяца.
NOW() Возвращает текущую дату и время.
PERIOD_ADD() Добавляет интервал к месяцу-году.
PERIOD_DIFF() Возвращает число месяцев между двумя периодами.
QUARTER() Возвращает четверть часа из переданной даты в виде аргумента.
SEC_TO_TIME() Конвертирует секунды в формат ‘ЧЧ:MM:СС’.
SECOND() Возвращает секунду(0-59).
MySQL STR_TO_DATE() Преобразует строку в дату.
SUBTIME() Вычитает время.
SYSDATE() Возвращает время, в которое была выполнена функцию.
TIME_FORMAT() Задает формат времени.
TIME_TO_SEC() Возвращает аргумент, преобразованный в секунды.
TIME() Выбирает часть времени из выражения, передаваемого в виде аргумента.
TIMEDIFF() Вычитает время.
TIMESTAMP() С одним аргументом эта функцию возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов.
TIMESTAMPADD() Добавляет интервал к дате-времени.
TIMESTAMPDIFF() Вычитает интервал из даты — времени.
TO_DAYS() Возвращает аргумент даты, преобразованный в дни.
UNIX_TIMESTAMP() Извлекает дату-время в формате UNIX в формат, принимаемый MySQL.
UTC_DATE() Возвращает текущую дату по универсальному времени(UTC).
UTC_TIME() Возвращает текущее время по универсальному времени(UTC).
UTC_TIMESTAMP() Возвращает текущую дату-время по универсальному времени(UTC).
WEEK() Возвращает номер недели.
WEEKDAY() Возвращает индекс дня недели.
WEEKOFYEAR() Возвращает календарную неделю даты(1-53).
YEAR() Возвращает год.
YEARWEEK() Возвращает год и неделю.

Вы можете поэкспериментировать с данными возможностями MySQL date format, даже не занося никаких данных в таблицу. К примеру:

mysql> SELECT NOW();+---------------------+| NOW() |+---------------------+| 2007-10-23 11:46:31 |+---------------------+1 row in set(0.00 sec)

Вы можете без проблем попробовать сочетание нескольких возможностей в одном запросе(к примеру, чтобы найти день недели):

mysql> SELECT MONTHNAME(NOW());+------------------+| MONTHNAME(NOW()) |+------------------+| October |+------------------+1 row in set(0.00 sec)

Внесение значений даты и времени в столбцы таблицы

Рассмотрим, как вносятся значения date MySQL в таблицу. Чтобы продемонстрировать это, мы продолжим использовать таблицу orders, которую создали в начале статьи.

Мы начнем с добавления новой строки заказа. Значение поля ввода order_no будет автоматом увеличиваться на 1, поэтому нам остается вставить значения order_item, дату создания заказа и дату доставки. Дата заказа — это время, в которое вставляется заказ, так что мы можем использовать возможность NOW(), чтобы внести в строку текущую дату и время.

Дата доставки — это период времени после даты заказа, которую мы можем вернуть, используя возможность MySQL DATE_ADD(), которая принимает в виде аргументов дату начала(в нашем случае NOW()) и INTERVAL (в нашем случае 14 дней). Например:

INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('iPhone 8Gb', NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY));

Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в виде даты доставки:

mysql> SELECT * FROM orders;+----------+------------+---------------------+----------------+| order_no | order_item | order_date | order_delivery |+----------+------------+---------------------+----------------+|        1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06     |+----------+------------+---------------------+----------------+1 row in set (0.00 sec)

Точно так же можно легко заказать товар с датой доставки через два месяца:

mysql> INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('ipod Touch 4Gb', NOW(), DATE_ADD(NOW(), INTERVAL 2 MONTH));Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM orders;+----------+----------------+---------------------+----------------+| order_no | order_item     | order_date          | order_delivery |+----------+----------------+---------------------+----------------+|        1 | iPhone 8Gb     | 2007-10-23 11:37:55 | 2007-11-06     ||        2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23     |+----------+----------------+---------------------+----------------+2 rows in set (0.00 sec)

Извлечение данных по дате и времени

В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. К примеру, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:

mysql> SELECT * FROM orders WHERE MONTHNAME(order_delivery) = 'November';+----------+------------+---------------------+----------------+| order_no | order_item | order_date          | order_delivery |+----------+------------+---------------------+----------------+|        1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06     |+----------+------------+---------------------+----------------+1 row in set (0.00 sec)

Точно так же мы можем использовать BETWEEN , чтобы выбрать товары, доставка которых произойдет между двумя указанными датами. К примеру:

mysql> SELECT * FROM orders WHERE order_delivery BETWEEN '2007-12-01' AND '2008-01-01';+----------+----------------+---------------------+----------------+| order_no | order_item     | order_date          | order_delivery |+----------+----------------+---------------------+----------------+|        2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23     |+----------+----------------+---------------------+----------------+1 row in set (0.03 sec)

Заключение

В данной статье мы рассмотрели форматы, используемые для определения даты и времени, и перечислили возможности, используемые в для операций в MySQL с тип DATE . А также пару примеров внесения и извлечения данных.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *