Изучаем 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() | Добавляет одну дату к другой. |
DATE_FORMAT() | Задает указанный формат даты. |
DATE() | Извлекает часть даты из даты или выражения дата-время. |
DATEDIFF() | Вычитает одну дату из другой. |
DAYNAME() | Возвращает день недели. |
DAYOFMONTH() | Возвращает день месяца (1-31). |
DAYOFWEEK() | Возвращает индекс дня недели из аргумента. |
DAYOFYEAR() | Возвращает день года (1-366). |
EXTRACT() | Извлекает часть даты. |
FROM_DAYS() | Преобразует номер дня в дату. |
FROM_UNIXTIME() | Задает формат даты в формате UNIX. |
DATE_SUB() | Вычитает одну дату из другой. |
HOUR() | Извлекает час. |
LAST_DAY() | Возвращает последний день месяца для аргумента. |
MAKEDATE() | Создает дату из года и дня года. |
MAKETIME() | Возвращает значение времени. |
MICROSECOND() | Возвращает миллисекунды из аргумента. |
MINUTE() | Возвращает минуты из аргумента. |
MONTH() | Возвращает месяц из переданной даты. |
MONTHNAME() | Возвращает название месяца. |
NOW() | Возвращает текущую дату и время. |
PERIOD_ADD() | Добавляет интервал к месяцу-году. |
PERIOD_DIFF() | Возвращает количество месяцев между двумя периодами. |
QUARTER() | Возвращает четверть часа из переданной даты в качестве аргумента. |
SEC_TO_TIME() | Конвертирует секунды в формат 'ЧЧ:MM:СС'. |
SECOND() | Возвращает секунду (0-59). |
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. А также несколько примеров внесения и извлечения данных.