如何在SQL中处理日期和时间
介绍
在使用关系数据库和结构化查询语言 (SQL) 时，有时您可能需要使用表示特定日期或时间的值。 例如，您可能需要计算在某项活动上花费的总小时数，或者您可能需要使用数学运算符和聚合函数来操作日期或时间值，以计算它们的总和或平均值。
在本教程中，您将学习如何在 SQL 中使用日期和时间。 您将从执行算术开始，并仅使用 SELECT 语句使用具有日期和时间的各种函数。 然后您将通过对示例数据运行查询来练习，您将学习如何实现 CAST 函数以使输出更易于阅读。
先决条件
要完成本教程，您需要：
- 运行 Ubuntu 20.04 的服务器，非 root 用户具有
sudo管理权限并启用了防火墙。 按照我们的 使用 Ubuntu 20.04 的初始服务器设置开始。 - 在服务器上安装并保护 MySQL。 按照我们的 如何在 Ubuntu 20.04 上安装 MySQL 指南进行设置。 本指南假定您还设置了非 root MySQL 用户，如本指南的 Step 3 中所述。
注意： 请注意，许多关系数据库管理系统使用自己独特的 SQL 实现。 尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。
要在本教程中练习使用日期和时间，您需要一个加载了示例数据的数据库和表。 如果您没有准备好插入，您可以阅读以下 连接到 MySQL 并设置示例数据库 部分，以了解如何创建数据库和表。 本教程将自始至终引用此示例数据库和表。
您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。 单击下面的Launch an Interactive Terminal!按钮开始使用。
启动交互式终端！
连接到 MySQL 并设置示例数据库
如果您的 SQL 数据库在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：
ssh sammy@your_server_ip
接下来，打开 MySQL 提示符，将 sammy 替换为您的 MySQL 用户帐户信息。 如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：
mysql -u sammy -p
创建一个名为 datetimeDB 的数据库：
CREATE DATABASE datetimeDB;
如果数据库创建成功，您将收到以下输出：
OutputQuery OK, 1 row affected (0.01 sec)
要选择 datetimeDB 数据库，请运行以下 USE 语句：
USE datetimeDB;
OutputDatabase changed
选择数据库后，在其中创建一个表。 对于本教程的示例，我们将创建一个表格，其中包含两名跑步者在一年内参加的各种比赛的结果。 该表将包含以下七列：
race_id：显示int数据类型的值，并用作表的 主键 ，这意味着该列中的每个值将用作其各自行的唯一标识符。runner_name：使用varchar数据类型，最多 30 个字符作为两个赛车手 Bolt 和 Felix 的名称。race_name：保存数据类型为varchar的比赛类型，最多20个字符。start_day：使用DATE数据类型按年、月和日跟踪特定比赛的日期。 此数据类型遵循以下参数：四位数字表示年份，最多两位数字表示月份和日期 (YYYY-MM-DD)。start_time：以小时、分钟和秒（HH:MM:SS）表示数据类型为TIME的比赛开始时间。 此数据类型遵循 24 小时制格式，例如15:00相当于下午 3:00。total_miles：使用decimal数据类型显示每场比赛的总里程数，因为每场比赛的总里程数很多不是整数。 在这种情况下，decimal指定精度为 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');
OutputQuery OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
插入数据后，您就可以开始在 SQL 中练习一些带有日期和时间的算术和函数了。
对日期和时间使用算术
在 SQL 中，您可以使用 数学表达式 操作日期和时间值。 所需要的只是数学运算符和您要计算的值。
例如，假设您想查找一个日期与另一个日期相隔一定天数。 以下查询采用一个日期值 (2022-10-05) 并将 17 添加到它以返回查询中指定日期后 17 天的日期值。 请注意，此示例将 2022-10-05 指定为 DATE 值，以确保 DBMS 不会将其解释为字符串或其他数据类型：
SELECT DATE '2022-10-05' + 17 AS new_date;
Output+----------+ | new_date | +----------+ | 20221022 | +----------+ 1 row in set (0.01 sec)
如此输出所示，2022-10-05 之后的 17 天是 2022-10-22，即 2022 年 10 月 22 日。
作为另一个示例，假设您要计算两个不同时间之间的总小时数。 您可以通过将两次相减来做到这一点。 对于以下查询，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 小时。
现在练习对样本数据中的日期和时间信息使用算术运算。 对于第一个查询，通过从 start_time 中减去 end_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 函数来转换这些数据值，使它们更清晰易读。
现在，如果您只对每个跑步者在较长比赛中的表现感兴趣，例如半程马拉松和全程马拉松，您可以查询您的数据以检索该信息。 对于此查询，从 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)
正如此输出所示，从 current_date（撰写本文时）开始的 11 天前是 2022-02-06，即 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，尽管大多数都有 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 和聚合函数
回想 Using Arithmetic with Dates and Times 部分中的第三个示例，当您运行以下查询以从 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 列转换为 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)
此输出显示每个跑步者的最短跑步时间，在这种情况下，Bolt 至少为 6 分 30 秒，Felix 为 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 秒； 而菲利克斯一共是四小时两分十秒。
现在让我们查询一些关于每个跑步者跑步总小时数的高级信息。 对于此查询，结合 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 的解释，它实际上是以整数计算总时间。 如果我们将这些结果解读为时间，博尔特的总时间分解为 5 小时 28 分 80 秒； 费利克斯的时间分解为 7 小时 61 分 49 秒。 如您所知，这种时间细分没有意义，这表明它是以整数而不是时间计算的。 如果您在不同的 DBMS（例如 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 秒； 而博尔特的时间是 6 小时 9 分 20 秒。 这是一个示例，说明各种 DBMS 实现如何以不同方式解释数据值，即使它使用相同的查询和数据集。
结论
在查询分钟、秒、小时、天、月、年等特定结果时，了解如何在 SQL 中使用日期和时间很有用； 或所有这些的组合。 此外，还有许多可用于日期和时间的函数，可以更轻松地查找某些值，例如当前日期或时间。 虽然本教程仅对 SQL 中的日期和时间使用了加法和减法运算，但您可以将日期和时间值与任何数学表达式一起使用。 从我们的 数学表达式和聚合函数 指南中了解更多信息，并在您的日期和时间查询中试用它们。
