如何在SQL中处理日期和时间

教程
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。按照我们的在Ubuntu 20.04上安装MySQL指南进行设置。本指南假设您还设置了一个非root MySQL用户，如本指南的第3步所述。

注意：请注意，许多关系型数据库管理系统使用其自己独特的SQL实现。尽管本教程中概述的命令在大多数RDBMS上都能正常工作，但如果您在MySQL之外的系统上测试它们，确切的语法或输出可能会有所不同。

在本教程中练习使用日期和时间，您需要一个加载了示例数据的数据库和表。如果您还没有准备好插入数据的数据库，您可以阅读以下连接到MySQL并设置示例数据库部分，了解如何创建数据库和表。本教程将在后续内容中参考此示例数据库和表。

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

如果您的SQL数据库运行在远程服务器上，请从本地计算机通过SSH连接到服务器：

  1. ssh sammy@your_server_ip

接下来，打开MySQL提示符，将sammy替换为您的MySQL用户帐户信息：

  1. mysql -u sammy -p

创建名为datetimeDB的数据库：

  1. CREATE DATABASE datetimeDB;

如果数据库创建成功，您将收到以下输出：

Output
Query OK, 1 row affected (0.01 sec)

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

  1. USE datetimeDB;



Output
Database changed

选择数据库后，在其中创建一个表。在本教程的示例中，我们将创建一个表，用于记录一年内两名选手参加的各种比赛结果。此表将包含以下七列：

  • race_id：显示int数据类型的值，并充当表的主键，意味着该列中的每个值将作为其各自行的唯一标识符。
  • runner_name：使用varchar数据类型，最多可容纳30个字符，用于两名选手Bolt和Felix的姓名。
  • race_name：使用varchar数据类型，最多可容纳20个字符，用于保存比赛类型。
  • start_day：使用DATE数据类型，通过年、月和日跟踪特定比赛的日期。此数据类型遵循以下参数：年份为四位数，月份和日期最多为两位数（YYYY-MM-DD）。
  • start_time：表示比赛开始时间，使用TIME数据类型，按小时、分钟和秒计算（HH:MM:SS）。此数据类型遵循24小时制格式，例如15:00表示下午3点。
  • total_miles：使用decimal数据类型显示每场比赛的总里程，因为许多比赛的总里程不是整数。在这种情况下，decimal指定了三位精度和一位标度，这意味着该列中的任何值都可以有三位数字，其中一位位于小数点右侧。
  • end_time：使用TIMESTAMP数据类型来跟踪比赛结束时的跑步者时间。这种数据类型将日期和时间合并为一个字符串，其格式是DATE和TIME的组合：（YYYY-MM-DD HH:MM:SS）。

通过运行CREATE TABLE命令创建表：

  1. CREATE TABLE race_results (

  2. race_id int, 

  3. runner_name varchar(30),

  4. race_name varchar(20), 

  5. start_day DATE,

  6. start_time TIME, 

  7. total_miles decimal(3, 1),

  8. end_time TIMESTAMP,

  9. PRIMARY KEY (race_id)

  10. );

接下来将一些示例数据插入空表：

  1. INSERT INTO race_results

  2. (race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)

  3. VALUES

  4. (1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),

  5. (2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),

  6. (3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),

  7. (4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),

  8. (5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),

  9. (6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),

  10. (7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),

  11. (8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),

  12. (9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),

  13. (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值，以确保数据库管理系统不会将其解释为字符串或其他数据类型：

  1. 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值，以便返回小时的差异：

  1. 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来计算每场比赛选手完成比赛所需的总时间：

  1. SELECT runner_name, race_name, end_time - start_time 

  2. AS total_time 

  3. 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英里的数据：

  1. SELECT runner_name, race_name, end_time - start_time AS half_full_results

  2. FROM race_results 

  3. 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函数组成，如下所示：

  1. SELECT current_date;



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

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

  1. SELECT current_time;



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

如果您希望在输出中查询日期和时间，请使用current_timestamp函数：

  1. SELECT current_timestamp;



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

您可以在类似于前一部分的算术函数中使用这些日期和时间函数。例如，假设您想知道11天前的日期是什么。在这种情况下，您可以使用与之前查询current_date函数并从中减去11以找到十一天前日期的相同语法结构：

  1. 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函数：

  1. 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表达式允许您找到给定日期或时间表达式之前或之后的一组间隔。它们必须采用以下形式：

Example interval expression
INTERVAL value unit

例如，要找到五天后的日期，您可以运行以下查询：

  1. 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)

这比以下查询要明确得多，尽管输出类似，但并非完全相同：

  1. SELECT current_date + 5 AS "5_days_from_today";



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

请注意，您还可以从日期或时间中减去间隔以查找指定日期值之前的值：

  1. 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：

  1. SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",

  2. current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",

  3. 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数据类型：

  1. SELECT runner_name, race_name, end_time - start_time 

  2. AS total_time 

  3. 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数据类型：

  1. SELECT runner_name, race_name, CAST(end_time - start_time AS time)

  2. AS total_time 

  3. 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列组织这些值，以便输出将呈现两名选手的比赛结果：

  1. SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time

  2. 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：

  1. SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time

  2. 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将这些数据值转换为TIME。不要忘记包括GROUP BY以组织每个跑步者的结果值：

  1. SELECT runner_name, SUM(CAST(end_time - start_time AS time))

  2. 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的总时间会被分解为五小时、28分钟和80秒；而Felix的时间则被分解为七小时、61分钟和49秒。正如你所看到的，这种时间的分解是没有意义的，这表明它被计算为整数而不是时间。如果你在不同的DBMS中尝试，比如PostgreSQL，同样的查询会略有不同：

  1. SELECT runner_name, SUM(CAST(end_time - start_time AS time))

  2. 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小时、一分钟和44秒；而Bolt的结果是六小时、九分钟和20秒。这是各种DBMS实现可能会以不同方式解释数据值的一个例子，即使使用相同的查询和数据集。

结论

了解如何在SQL中使用日期和时间是有用的，当查询特定结果时，如分钟、秒、小时、天、月、年；或者它们的组合。此外，有许多可用于日期和时间的函数，使得查找特定值更加容易，比如当前日期或时间。虽然本教程仅在SQL中使用了日期和时间的加法和减法运算，但您可以将日期和时间值与任何数学表达式一起使用。从我们的指南中了解更多关于数学表达式和聚合函数，并尝试在您的日期和时间查询中使用它们。

Source:
https://www.digitalocean.com/community/tutorials/how-to-work-with-dates-and-times-in-sql