Как работать с датами и временем в SQL

Введение

При работе с реляционными базами данных и языком структурированных запросов (SQL) могут возникнуть ситуации, когда вам потребуется работать со значениями, представляющими конкретные даты или время. Например, вам может потребоваться рассчитать общее количество часов, затраченных на определённую деятельность, или агрегировать значения даты или времени с помощью математических операторов и функций для вычисления их суммы или среднего значения.

В этом руководстве вы научитесь использовать даты и время в SQL. Вы начнёте с выполнения вычислений и применения различных функций с датами и временем, используя только оператор SELECT. Затем вы попрактикуетесь, выполняя запросы к образцам данных, и научитесь применять функцию CAST, чтобы сделать вывод более удобочитаемым.

Предпосылки

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

• Сервер под управлением Ubuntu 20.04 с пользователем без прав root, обладающим правами администратора sudo и включенным брандмауэром.
• MySQL установлен и защищен на сервере.

Примечание: Обратите внимание, что многие системы управления реляционными базами данных используют собственные уникальные реализации SQL. Хотя команды, упомянутые в этом руководстве, будут работать в большинстве СУБД, точный синтаксис или вывод могут отличаться при использовании их в системе, отличной от MySQL.

Для практики использования дат и времени в этом руководстве вам понадобятся база данных и таблица с образцами данных. Если у вас нет готовой базы данных для добавления, ознакомьтесь с разделом «Подключение к MySQL и настройка образца базы данных», чтобы узнать, как создать базу данных и таблицу. В этом руководстве будут использоваться эти образцы базы данных и таблицы.

Подключение к MySQL и настройка образца базы данных

Если ваша база данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локальной машины:

ssh sammy@your_server_ip

Затем откройте командную строку MySQL и замените Sami данными своей учетной записи MySQL:

mysql -u sammy -p

Создайте базу данных с именем datetimeDB:

CREATE DATABASE datetimeDB;

Если база данных создана успешно, вы получите следующий вывод:

Output
Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных datetimeDB, выполните следующий оператор USE:

USE datetimeDB;

Output
Database changed

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

• race_id: представляет значения типа данных int и действует как первичный ключ таблицы, то есть каждое значение в этом столбце действует как уникальный идентификатор для соответствующей строки.
• runner_name: Использует тип данных varchar с максимальной длиной 30 символов для имен двух бегунов Болта и Феликса.
• race_name: Содержит типы рас с типом данных varchar длиной до 20 символов.
• start_day: использует тип данных DATE для отслеживания даты конкретного матча по году, месяцу и дню. Этот тип данных соответствует следующим параметрам: четыре цифры для года и максимум две цифры для месяца и дня (ГГГГ-ММ-ДД).
• start_time: представляет время начала матча с типом данных TIME в часах, минутах и секундах (ЧЧ:ММ:СС). Этот тип данных соответствует 24-часовому формату времени, например, 15:00 соответствует 15:00.
• total_miles: отображает общий пробег для каждой гонки с использованием десятичного типа данных, поскольку многие значения общего пробега за гонку не являются целыми числами. В данном случае десятичная дробь указывает точность три с шагом один, что означает, что каждое значение в этом столбце может состоять из трёх цифр, одна из которых находится справа от десятичной точки.
• end_time: использует тип данных TIMESTAMP для отслеживания времени бегунов по окончании забега. Этот тип данных объединяет дату и время в строку, а его формат представляет собой комбинацию ДАТЫ и ВРЕМЕНИ: (ГГГГ-ММ-ДД ЧЧ:ММ:СС).

Создайте таблицу, выполнив команду CREATE TABLE:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

Затем введите некоторые примеры данных в пустую таблицу:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

После ввода данных вы готовы приступить к практике арифметических действий и функций с датами и временем в SQL.

Использование учетной записи с датой и временем

В SQL вы можете манипулировать значениями даты и времени, используя математические выражения. Всё, что вам нужно, — это математический оператор и значения, которые вы хотите вычислить.

Например, предположим, что вам нужно найти дату, отстоящую на указанное количество дней друг от друга. Следующий запрос принимает значение даты (2022-10-05) и добавляет к нему 17, чтобы вернуть значение даты, отстоящей на семнадцать дней от даты, указанной в запросе. Обратите внимание, что в этом примере в качестве значения DATE указано 2022-10-05, чтобы СУБД не интерпретировала его как строку или другой тип данных:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

Как видно из этих данных, через 17 дней после 05.10.2022 г. наступит 22.10.2022 г., или 22 октября 2022 г.

В качестве другого примера предположим, что вам нужно рассчитать общее количество часов между двумя разными моментами времени. Это можно сделать, вычтя два значения времени друг из друга. В следующем запросе 11:00 — первое значение времени, а 3:00 — второе. Здесь вам нужно указать, что оба значения являются значениями типа TIME, чтобы вернуть разницу в часах:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
| 80000 |
+-----------+
1 row in set (0.00 sec)

Этот вывод показывает, что разница между 11:00 и 3:00 составляет 80 000, или 8 часов.

Теперь попрактикуйтесь в вычислениях с использованием данных о дате и времени из выборки. Для первого запроса рассчитайте общее время, затраченное бегунами на финиш каждой дистанции, вычтя время_окончания из времени_начала:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name | total_time |
+-------------+---------------+----------------+
| bolt | 1600_meters | 20220918000630 |
| bolt | 5K | 20221019002231 |
| bolt | 10K | 20221120003805 |
| bolt | half_marathon | 20221221013904 |
| bolt | full_marathon | 20230122032310 |
| felix | 1600_meters | 20220918000715 |
| felix | 5K | 20221019003050 |
| felix | 10K | 20221120011017 |
| felix | half_marathon | 20221221021157 |
| felix | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Вы заметите, что вывод в столбце total_time довольно длинный и его трудно читать. Далее мы покажем, как использовать функцию CAST для преобразования этих значений данных в более понятный формат.

Теперь, если вас интересуют только результаты каждого бегуна на более длинных дистанциях, например, полумарафоне или полном марафоне, вы можете выполнить запрос к своим данным для получения этой информации. Для этого запроса вычтите время_окончания из времени_старта и ограничьте результаты с помощью предложения WHERE, чтобы получить данные, где total_miles превышает 12:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name | half_full_results |
+-------------+---------------+-------------------+
| bolt | half_marathon | 20221221013904 |
| bolt | full_marathon | 20230122032310 |
| felix | half_marathon | 20221221021157 |
| felix | full_marathon | 20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

В этом разделе вы выполнили несколько вычислений даты и времени с помощью оператора SELECT и на практике на примере данных. Далее вы попрактикуетесь в выполнении запросов с использованием различных функций даты и времени.

Использование функций даты и времени и выражений диапазона

В SQL существует несколько функций для поиска и обработки значений даты и времени. Функции SQL обычно используются для обработки и обработки данных, а доступные функции зависят от реализации SQL. Однако большинство реализаций SQL позволяют найти текущую дату и время, запросив значения current_date и current_time.

Например, чтобы найти сегодняшнюю дату, синтаксис короткий и состоит только из оператора SELECT и функции current_date, например:

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15 |
+--------------+
1 row in set (0.00 sec)

Используя тот же синтаксис, вы можете узнать текущее время с помощью функции current_time:

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20 |
+--------------+
1 row in set (0.00 sec)

Если вы предпочитаете запрашивать дату и время в выходных данных, используйте функцию current_timestamp:

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

Подобные функции даты и времени можно использовать в арифметических функциях, аналогичных функциям из предыдущего раздела. Например, вам нужно узнать дату 11 дней назад. В этом случае вы можете использовать тот же синтаксис, что и для запроса функции current_date, а затем вычесть из неё 11, чтобы найти дату одиннадцатидневной давности:

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
| 20220206 |
+-------------------+
1 row in set (0.01 sec)

Как показывает этот вывод, за 11 дней до current_date (на момент написания этой статьи) было 2022-02-06, или 6 февраля 2022 года. Теперь попробуйте выполнить ту же операцию, но замените current_date функцией current_time:

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
| 233639 |
+-------------------+
1 row in set (0.00 sec)

Этот вывод показывает, что при вычитании 11 из значения current_time получается 11 секунд. Операция, которую вы выполнили ранее с помощью функции current_date, интерпретировала 11 как дни, а не секунды. Эта непоследовательность в интерпретации чисел может сбивать с толку при работе с функциями даты и времени. Вместо того, чтобы манипулировать значениями даты и времени с помощью подобных вычислений, многие системы управления базами данных позволяют делать это более явно с помощью операторов INTERVAL.

Выражения INTERVAL позволяют указать дату или время до или после указанного интервала времени из выражения даты или времени. Они должны иметь следующий вид:

INTERVAL value unit

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

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

В этом примере значение current_date находит значение, а затем к нему добавляется выражение интервала INTERVAL '5' DAY. Возвращается дата, отстоящая на 5 дней вперёд:

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06 |
+-------------------+
1 row in set (0.00 sec)

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

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 20220306 |
+-------------------+
1 row in set (0.00 sec)

Обратите внимание, что вы можете вычитать временные интервалы из дат или времени, чтобы найти значения до указанного значения даты:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01 |
+--------------+
1 row in set (0.00 sec)

Какие единицы измерения доступны для использования в операторах INTERVAL, зависит от вашего выбора СУБД, хотя большинство из них имеют такие параметры, как ЧАС, МИНУТА и СЕКУНДА:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43 | 01:46:43 | 01:52:03.000000 |
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

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

Использование функций CAST и Aggregate с датами и временем

Вспомните третий пример из раздела «Использование арифметики с датами и временем», когда вы выполнили следующий запрос, чтобы вычесть end_time из start_time и рассчитать общее количество часов, пробежавших каждого бегуна в каждом забеге. Однако в результате получился столбец с очень длинными данными, следующими за типом данных TIMESTAMP, заданным в таблице:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name | total_time |
+-------------+---------------+----------------+
| bolt | 1600_meters | 20220918000630 |
| bolt | 5K | 20221019002231 |
| bolt | 10K | 20221120003805 |
| bolt | half_marathon | 20221221013904 |
| bolt | full_marathon | 20230122032310 |
| felix | 1600_meters | 20220918000715 |
| felix | 5K | 20221019003050 |
| felix | 10K | 20221120011017 |
| felix | half_marathon | 20221221021157 |
| felix | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Поскольку вы выполняете операцию над двумя столбцами с разными типами данных (end_time содержит значения TIMESTAMP, а start_time — значения TIME), база данных не знает, какой тип данных использовать при выводе результата операции. Вместо этого она преобразует оба значения в целые числа для выполнения операции, что приводит к появлению длинных чисел в столбце total_time.

Чтобы сделать эти данные более понятными для чтения и интерпретации, вы можете использовать функцию CAST для преобразования этих длинных целых чисел в тип данных TIME. Для этого начните с CAST, затем сразу за ним в открывающей скобке укажите значения, которые вы хотите преобразовать, а затем ключевое слово AS и тип данных, в который вы хотите их преобразовать.

Следующий запрос идентичен предыдущему примеру, но использует функцию CAST для преобразования столбца total_time в тип данных времени:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name | total_time |
+-------------+---------------+------------+
| bolt | 1600_meters | 00:06:30 |
| bolt | 5K | 00:22:31 |
| bolt | 10K | 00:38:05 |
| bolt | half_marathon | 01:39:04 |
| bolt | full_marathon | 03:23:10 |
| felix | 1600_meters | 00:07:15 |
| felix | 5K | 00:30:50 |
| felix | 10K | 01:10:17 |
| felix | half_marathon | 02:11:57 |
| felix | full_marathon | 04:02:10 |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST преобразовал значения данных в этом выводе во ВРЕМЯ, что сделало его очень удобным для чтения и понимания.

Теперь давайте используем несколько агрегатных функций в сочетании с функцией CAST, чтобы найти наименьшее, наибольшее и общее время каждого бегуна. Сначала найдем минимальное (или наименьшее) время, затраченное с помощью функции MIN. Опять же, для ясности, вам нужно использовать CAST для преобразования значений данных TIMESTAMP в значения данных TIME. Обратите внимание, что при использовании двух функций, как в этом примере, требуются две пары скобок, и расчет общего времени (время_окончания - время_начала) должен быть помещен в одну из них. Наконец, добавьте предложение GROUP BY, чтобы упорядочить эти значения по столбцу runner_name для отображения результатов забега бегуна:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt | 00:06:30 |
| felix | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

В этом выводе показано наименьшее время бегуна, в данном случае не менее шести минут и 30 секунд для Болта и семи минут и 15 секунд для Феликса.

Затем найдите максимальное время выполнения для каждого участника. Вы можете использовать тот же синтаксис, что и в предыдущем операторе, но на этот раз замените MIN на MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt | 03:23:10 |
| felix | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

Согласно этим данным, максимальное время бега Болта составило три часа 23 минуты и 10 секунд. А у Феликса — четыре часа две минуты и 10 секунд.

Теперь запросим общую информацию об общем количестве часов, потраченных каждым бегуном. Для этого запроса объедините функцию SUM, чтобы найти общее количество часов на основе end_time и start_time, и используйте CAST для преобразования этих значений во TIME. Не забудьте добавить GROUP BY для организации результатов для каждого бегуна:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt | 52880 |
| felix | 76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Интересно, что этот вывод показывает, что MySQL интерпретирует общее время как целое число. Если рассматривать эти результаты как время, общее время Болта будет разделено на пять часов, 28 минут и 80 секунд. А время Феликса — на семь часов, 61 минуту и 49 секунд. Как видите, это не логическое разбиение времени, что указывает на то, что оно вычисляется как целое число, а не как время. Например, если попробовать это в другой СУБД, например, в PostgreSQL, тот же запрос будет выглядеть немного иначе:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
runner_name | total_hours
-------------+-------------
felix | 10:01:44
bolt | 06:09:20
(2 rows)

В этом случае запрос в PostgreSQL интерпретирует значения как время и вычисляет их соответствующим образом, так что результаты Феликса составляют в общей сложности 10 часов, одну минуту и 44 секунды. А результаты Болта — шесть часов, девять минут и 20 секунд. Это пример того, как разные реализации СУБД могут по-разному интерпретировать значения данных, даже если используют один и тот же запрос и набор данных.

Результат

Понимание того, как использовать даты и время в SQL, полезно при поиске конкретных результатов, таких как минуты, секунды, часы, дни, месяцы, годы. Или их комбинации. Кроме того, существует множество функций для дат и времени, которые упрощают поиск конкретных значений, таких как текущая дата или время. Хотя в этом руководстве рассматриваются только сложение и вычитание дат и времени в SQL, вы можете использовать значения даты и времени в любых математических выражениях. Узнайте больше в нашем руководстве по математическим выражениям и функциям сложения и попробуйте их использовать для поиска дат и времени.