介绍
在使用关系数据库和结构化查询语言 (SQL) 时，有时需要处理代表特定日期或时间的值。例如，可能需要计算某项活动的总耗时，或者需要使用数学运算符和函数聚合日期或时间值，以计算它们的总和或平均值。.
在本教程中，您将学习如何在 SQL 中使用日期和时间。首先，您将学习如何仅使用 SELECT 语句对日期和时间进行计算并使用各种函数。然后，您将通过对示例数据运行查询进行练习，并学习如何实现 CAST 函数，使输出结果更易于阅读。.
先决条件
要完成本教程，您需要：
- 一台运行 Ubuntu 20.04 的服务器，使用具有 sudo 管理权限的非 root 用户，并启用了防火墙。.
- 服务器上已安装并配置了 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 个字符，用于表示两位跑者 Bolt 和 Felix 的姓名。.
- race_name：保存最多 20 个字符的 varchar 数据类型的种族类型。.
- start_day：使用 DATE 数据类型，根据年、月、日跟踪特定比赛的日期。此数据类型遵循以下参数：年份为四位数，月份和日期最多为两位数（YYYY-MM-DD）。.
- start_time：表示比赛的开始时间，数据类型为 TIME，单位为小时、分钟和秒 (HH:MM:SS)。此数据类型遵循 24 小时制，例如 15:00 表示下午 3:00。.
- total_miles：显示每场比赛的总里程数，使用十进制数据类型，因为很多比赛的总里程数并非整数。在这种情况下，十进制数的精度为 3，小数位数为 1，这意味着此列中的每个值都可以有三位数字，其中一位位于小数点右侧。.
- 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，返回比查询中指定的日期晚 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)如输出所示，2022 年 10 月 5 日之后的 17 天是 2022 年 10 月 22 日，即 2022 年 10 月 22 日。.
再举一个例子，假设你想计算两个不同时间点之间的总小时数。你可以通过将这两个时间点相减来实现。对于以下查询，11:00 是第一个时间值，3:00 是第二个时间值。这里你需要指定这两个值都是时间值，才能返回小时数的差值：
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 函数转换这些数据值，使其更易于阅读。.
如果您只对每位跑者在长距离比赛（例如半程马拉松和全程马拉松）中的表现感兴趣，您可以查询数据来检索该信息。对于此查询，请从 start_time 中减去 end_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 即可找到 11 天前的日期：
SELECT current_date - 11;
Output
+-------------------+
| current_date - 11 |
+-------------------+
| 20220206 |
+-------------------+
1 row in set (0.01 sec)如输出所示，当前日期（撰写本文时）之前的 11 天是 2022 年 2 月 6 日，即 2022 年 2 月 6 日。现在尝试相同的操作，但将 current_date 替换为 current_time 函数：
SELECT current_time - 11;
Output
+-------------------+
| current_time - 11 |
+-------------------+
| 233639 |
+-------------------+
1 row in set (0.00 sec)此输出表明，从 current_time 值中减去 11 时，实际减去的是 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 语句中可使用的单位取决于您选择的数据库管理系统 (DBMS)，不过大多数 DBMS 都会提供诸如 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 和聚合函数处理日期和时间
回想一下“使用日期和时间进行算术运算”部分中的第三个示例，当时您运行了以下查询，用 start_time 减去 end_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 数据值。请注意，当像本例中一样使用两个函数时，需要两对括号，并且总用时（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)该输出结果显示了跑步者的最短时间，在本例中，博尔特的最短时间为 6 分 30 秒，菲利克斯的最短时间为 7 分 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)输出结果显示，Bolt 的最长运行时间为 3 小时 23 分 10 秒，而 Felix 的最长运行时间为 4 小时 2 分 10 秒。.
现在我们来查询每位跑者跑步总时长的概要信息。对于此查询，请使用 SUM 函数根据 end_time - start_time 计算总时长，并使用 CAST 函数将这些数据值转换为时间类型。别忘了添加 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 将总时间解释为整数。如果我们把这些结果解读为时间，Bolt 的总时间将被分为 5 小时 28 分 80 秒，而 Felix 的时间将被分为 7 小时 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 中的查询会将这些值解释为时间并进行相应的计算，因此 Felix 的成绩总计为 10 小时 1 分 44 秒，而 Bolt 的成绩为 6 小时 9 分 20 秒。这说明即使使用相同的查询和数据集，不同的数据库管理系统 (DBMS) 实现也可能对数据值的解释有所不同。.
结果
了解如何在 SQL 中使用日期和时间对于搜索特定结果（例如分钟、秒、小时、天、月、年，或它们的组合）非常有用。此外，SQL 还提供了许多日期和时间函数，可以更轻松地查找特定值，例如当前日期或时间。虽然本教程仅涵盖 SQL 中日期和时间的加减运算，但您可以使用任何数学表达式来处理日期和时间值。请参阅我们的数学表达式和加法函数指南，了解更多信息，并将其应用于您的日期和时间搜索中。.
