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

Введение

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

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

Предварительные требования

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

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• Установленная и защищенная MySQL на сервере. Следуйте нашему руководству Как установить MySQL на Ubuntu 20.04, чтобы настроить это. При этом руководстве предполагается, что вы также настроили неадминистративного пользователя MySQL, как описано в Шаге 3 этого руководства.

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

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

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

ssh sammy@your_server_ip

Затем откройте приглашение MySQL, заменив sammy на информацию о вашей учетной записи 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: показывает общий пробег для каждой гонки, используя тип данных decimal, поскольку многие значения общего пробега на гонку не являются целыми числами. В этом случае decimal указывает точность до трех знаков с одним знаком справа от десятичной точки.
• end_time: использует тип данных TIMESTAMP для отслеживания времени финиша бегунов в конце гонки. Этот тип данных объединяет и дату, и время в одной строке, и его формат представляет собой комбинацию форматов DATE и TIME: (YYYY-MM-DD HH:MM:SS).

Создайте таблицу, выполнив команду 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, чтобы вернуть значение для даты, семнадцать дней после указанной в запросе. Обратите внимание, что в этом примере 2022-10-05 указано как значение DATE, чтобы обеспечить то, что СУБД не будет интерпретировать его как строку или какой-либо другой тип данных:

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

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

Как указывает этот вывод, 17 дней после 2022-10-05 – это 2022-10-22, или 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 – 80000, или 8 часов.

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

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 для преобразования этих значений данных, чтобы они стали более понятными при чтении.

Теперь, если вас интересует только выступление каждого бегуна на более длинных дистанциях, таких как полумарафоны и марафоны, вы можете запросить данные для получения этой информации. Для этого запроса вычтите end_time из start_time, и уточните результаты, используя оператор 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 зависят от вашей выбора СУБД, хотя большинство из них будут иметь опции, такие как HOUR, MINUTE и SECOND:

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 и агрегатных функций с датой и временем

Вспомните третий пример из раздела Использование арифметики с датами и временем, когда вы выполняли следующий запрос для вычитания 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 в тип данных 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 преобразовала данные в тип TIME в этом выводе, что делает его намного более удобочитаемым для чтения и понимания.

Теперь давайте используем несколько агрегатных функций в сочетании с функцией CAST, чтобы найти самые короткие, самые длинные и общие результаты времени каждого бегуна. Сначала запросим минимальное (или самое короткое) количество времени с использованием агрегатной функции MIN. Снова вам потребуется использовать CAST для преобразования значений данных TIMESTAMP в значения данных TIME для ясности. Обратите внимание, что при использовании двух функций, как в этом примере, требуются две пары скобок, а вычисление общего количества часов (end_time - start_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, вы можете использовать значения даты и времени с любым математическим выражением. Узнайте больше из нашего руководства по математическим выражениям и агрегатным функциям и попробуйте их в своих запросах даты и времени.