Функции для работы с датами и временем

https://metanit.com/sql/mysql/6.3.php

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

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)

PreviousCOUNT + DISTINCT NextАвтоматизируйте создание бэкапов

Last updated 1 year ago