如何使用SQL系列 之 如何在SQL中使用日期和时间

引言

在处理关系数据库和结构化查询语言(SQL),有时可能需要处理值代表具体的日期或时间。例如，您可能需要计算花费在某个活动上的总小时数，或者可能需要使用数学运算符和聚合函数操作日期或时间值，以计算它们的总和或平均值。

在本教程中，您将学习如何在SQL中使用日期和时间。你将从仅使用SELECT语句执行算术和使用日期和时间的各种函数开始。然后，你将通过在样本数据上运行查询来进行练习，并学习如何实现CAST函数，以使输出更易于阅读。

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

连接到MySQL并设置一个示例数据库

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器:

ssh sammy@your_server_ip
1

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称:

mysql -u sammy -p
1

创建一个名为datetimeDB的数据库:

CREATE DATABASE datetimeDB;
1

如果数据库成功创建,您将收到这样的输出:

OutputQuery OK, 1 row affected (0.01 sec)
1

要选择datetimeDB数据库，运行以下USE语句:

USE datetimeDB;
1

OutputDatabase changed
1

选择数据库后，在其中创建一个表。本教程的示例中,我们将创建一个表,其中包含两个跑步者的结果为各种比赛他们在每年张成的空间中运行。这个表将包含以下7列:

• race_id:显示int数据类型的值，并作为表的* 主键 *，这意味着这一列中的每个值都将作为其各自行的唯一标识符。
• runner_name:使用varchar数据类型，最多30个字符，表示两个选手的名字，Bolt和Felix。
• race_name:保存varchar数据类型的比赛类型，最多不超过20个字符。
• start_day:使用DATE数据类型来按年、月和日跟踪特定比赛的日期。该数据类型遵循以下参数:4位数字表示年份，最多2位数字表示月和日(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)
); 
1
2
3
4
5
6
7
8
9
10

接下来向空表中插入一些示例数据:

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');
1
2
3
4
5
6
7
8
9
10
11
12
13

OutputQuery OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
1
2

插入数据后，就可以开始在SQL中练习一些日期和时间的算术和函数了。

使用算术与日期和时间

在SQL中，可以使用数学表达式操作日期和时间值。所需要的只是数学运算符和要计算的值。

举个例子，假设你想找到一个日期在另一个日期的特定天数之后。下面的查询需要一个日期值(2022-10-05),并增添了17返回的值中指定的一个日期17天后查询。请注意，下面的例子将2022-10-05指定为DATE值，以确保DBMS不会将其解释为字符串或其他数据类型:

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

Output+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6

如输出所示，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;
1

Output+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6

这个输出告诉你，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;
1
2
3

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

你会注意到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;
1
2
3

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)
1
2
3
4
5
6
7
8
9

在本节中，我们使用SELECT语句对日期和时间进行了一些运算，并对示例数据进行了实际操作。接下来，我们将使用各种日期和时间函数练习查询。

使用日期和时间函数和区间表达式

SQL中有几个函数可用于查找和操作日期和时间值。SQL函数通常用于处理或操作数据，可用的函数取决于SQL的实现。然而，大多数SQL实现都允许你通过查询current_date和current_time值来找到当前日期和时间。

例如，要查找今天的日期，语法很短，仅由SELECT语句和current_date函数组成，如下所示:

SELECT current_date;
1

Output+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

使用相同的语法，你可以用current_time函数找到当前时间:

SELECT current_time;
1

Output+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

如果你更喜欢在输出中同时查询日期和时间，使用current_timestamp函数:

SELECT current_timestamp;
1

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

你可以在与上一节类似的算术函数中使用这些日期和时间函数。例如，假设你想知道11天前的日期是今天的日期。在这种情况下，你可以使用与之前查询current_date函数相同的语法结构，然后从中减去11来得到11天前的日期:

SELECT current_date - 11;
1

Output+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6

如输出所示，11天前的current_date(撰写本文时)是2022-02-06，即2022年2月6日。现在尝试运行相同的操作，但将current_date替换为current_time函数:

SELECT current_time - 11;
1

Output+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

这个输出表明，当你从current_time值中减去11时，它减去了11秒。你之前使用current_date函数运行的操作将11解释为天数，而不是秒。在使用日期和时间函数时，解释数字的方式不一致，这可能会令人困惑。许多数据库管理系统不需要你像这样使用算术运算来操作日期和时间值，而是通过使用INTERVAL表达式让你更加明确。

INTERVAL表达式允许你从给定的日期或时间表达式中找到设定时间间隔之前或之后的日期或时间。它们必须采用以下形式:

区间表达式示例

INTERVAL value unit
1

例如，要查找五天之后的日期，可以执行以下查询:

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

这个例子找到current_date的值，然后将间隔表达式INTERVAL '5' DAY添加到其中。这将返回5天后的日期:

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

与下面的查询相比，这个查询的歧义要少得多，后者的输出类似，但不完全相同:

SELECT current_date + 5 AS "5_days_from_today";
1

Output+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

注意，你也可以从日期或时间中减去间隔来找到指定日期值之前的值:

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

Output+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

在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";
1
2
3

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)
1
2
3
4
5
6

现在您已经学习了间隔表达式和一些日期和时间函数，继续练习使用在第一步中插入的示例数据。

使用日期和时间的校正和聚合函数

回忆一下在使用日期和时间的算术部分的第三个例子，当你运行下面的查询，用end_time减去start_time来计算每个运动员在每场比赛中完成的总小时数。然而，输出的结果是包含一个非常长的输出列，它紧跟在表中设置的TIMESTAMP数据类型之后:

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

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

因为你正在对具有不同数据类型的两列执行操作(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;
1
2
3

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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;
1
2

Output+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7

输出显示了每个跑步者的最短跑步时间，在本例中，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;
1
2

Output+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7

这个输出告诉我们，Bolt的最长运行时间是3小时23分10秒;而菲利克斯总共花了4小时2分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;
1
2

Output+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7

有趣的是，上面的输出显示了对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;
1
2

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

在这种情况下,查询在PostgreSQL解释时间和计算的值,所以,菲利克斯的结果分解共有10个小时,一分钟,44秒;博尔特是6小时9分20秒。这个例子说明了即使使用相同的查询和数据集，不同的DBMS实现对数据值的解释也可能不同。

总结

了解如何使用SQL中的日期和时间对于查询特定的结果是很有用的，例如分钟、秒、小时、天、月、年;或者是所有这些的组合。此外，还有许多用于日期和时间的函数，可以更容易地查找某些值，如当前日期或时间。虽然本教程只在SQL中使用日期和时间的加减法运算，但您可以在任何数学表达式中使用日期和时间值。从我们的数学表达式和聚合函数指南中了解更多信息，并将它们用于日期和时间查询。