Работа с датой и временем в 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:22
20081023103722
2008/10/23 10.37.22
2008*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. А также несколько примеров внесения и извлечения данных.

Перевод статьи «Working with Dates and Times in MySQL» был подготовлен дружной командой проекта Сайтостроение от А до Я.