介绍
使用关系数据库和结构化查询语言(SQL)工作时,可能有时需要使用代表特定日期或时间的值工作,例如,您可能需要计算在特定活动上花费的总小时,或者您需要使用数学运算符操作日期或时间值,并汇总函数来计算其总和或平均值。
在本教程中,您将学习如何在 SQL 中使用日期和时间. 您将开始执行算法并使用各种函数与日期和时间仅使用SELECT语句。
前提条件
要完成本教程,您将需要:
- 运行 Ubuntu 20.04 的服务器,具有
sudo管理权限和启用防火墙的非 root 用户。 遵循我们的 Ubuntu 20.04 初始服务器设置以启动 - MySQL 安装并在服务器上安全。 遵循我们的 如何在 Ubuntu 20.04 上安装 MySQL指南来设置此功能。 本指南假定您也已设置了非 root MySQL 用户,如本指南的 步骤 3所述。
<$>[注] 注: 请注意,许多关系数据库管理系统使用自己的独特的SQL实现,尽管本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
要练习使用本教程中的日期和时间,你需要一个数据库和表加载的样本数据. 如果你没有一个准备好插入,你可以阅读以下 连接到MySQL和设置样本数据库部分,学习如何创建数据库和表.本教程将参考这个样本数据库和整个表。
连接到MySQL并设置样本数据库
如果您的 SQL 数据库在远程服务器上运行,则从本地计算机输入 SSH 到您的服务器:
1[environment local]
2ssh sammy@your_server_ip
接下来,打开MySQL提示,用您的MySQL用户帐户信息代替sammy:
1mysql -u sammy -p
创建一个名为datetimeDB的数据库:
1CREATE DATABASE datetimeDB;
如果数据库创建成功,您将收到以下输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
要选择datetimeDB数据库,运行以下USE语句:
1USE datetimeDB;
1[secondary_label Output]
2Database changed
选择数据库后,在其中创建一个表. 对于本教程的示例,我们将创建一个表,其中包含两名跑者在一年的范围内运行过的各种比赛的结果。
race-id':显示int ' 数据类型的值并充当表格的 [professional key(https://www.digitalocean.com/community/conceptual_articles/understanding-sql-constraints#primary-key]),意思是本列中的每个值将作为各自行的唯一标识符。 (-
)runner_name':对博尔特和费利克斯这两个赛车手的名字使用最多为30个字符的varchar'数据类型。 (-
)*race_name':将varchar数据类型的种族最多保留为20个字符。 (-) ) * " start_day":使用"DATE"数据类型,按年,月,日来跟踪特定赛事的日期. 此数据类型与以下参数相接:当年四位数,而当月和当日最多两位数("YYYY-MM-DD") (- ) * "start-time":以小时,分和秒来代表"TIME"数据类型("HH:MM:SS")的比赛开始时间. 此数据类型遵循24小时时钟格式,如"15:00"等值于平时3:00 (-) ) * " 总里程 " :使用 " 十进制 " 数据类型显示每个赛事的总里程,因为每个赛事的总里程中有许多不是整数。 在这种情况下,"十进制"指定精度为3个,比例尺为1,这意味着本列中的任何值都可以有三位数,其中一位数位为小数点右下方. (- ) * "end_time":使用"TIMESTAMP"数据类型来跟踪赛跑者在赛后的时间. 这种数据类型将日期和时间合并为一个字符串,其格式是DATE和TIME`的组合: ("YYY-MM-DD HH:MM:SS") (-
)
通过运行CREATE TABLE命令创建表:
1CREATE TABLE race_results (
2race_id int,
3runner_name varchar(30),
4race_name varchar(20),
5start_day DATE,
6start_time TIME,
7total_miles decimal(3, 1),
8end_time TIMESTAMP,
9PRIMARY KEY (race_id)
10);
接下来,将一些样本数据插入空表中:
1INSERT INTO race_results
2(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
3VALUES
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');
1[secondary_label Output]
2Query OK, 10 rows affected (0.00 sec)
3Records: 10 Duplicates: 0 Warnings: 0
一旦您输入了数据,您就可以开始练习 SQL 中的日期和时间的算法和函数。
使用日期和时间的算法
在 SQL 中,您可以使用 数学表达式来操纵日期和时间值。
例如,假设您想要找到一个日期,该日期是另一个日期之后的某些日期。下面的查询采取一个日期值(‘2022-10-05’),并添加‘17’,以返回日期在查询中指定的日期之后的十七天值。
1SELECT DATE '2022-10-05' + 17 AS new_date;
1[secondary_label Output]
2+----------+
3| new_date |
4+----------+
5| 20221022 |
6+----------+
71 row in set (0.01 sec)
正如此输出所示,在2022-10-05之后的17天是2022-10-22,即2022年10月22日。
另一个例子是,假设您想计算两个不同的时间之间的总时间。 您可以这样做,将两个时间从彼此中扣除。 对于下面的查询,‘11:00’是第一个时间值,而‘3:00’是第二个时间值。
1SELECT TIME '11:00' - TIME '3:00' AS time_diff;
1[secondary_label Output]
2+-----------+
3| time_diff |
4+-----------+
5| 80000 |
6+-----------+
71 row in set (0.00 sec)
这个输出告诉你, 11:00 和 3:00 之间的差异是 80000 或 8 小时。
现在练习使用样本数据中的日期和时间信息的算法。 对于第一个查询,从start_time中取出end_time来计算跑步者完成每个比赛所需的总时间:
1SELECT runner_name, race_name, end_time - start_time
2AS total_time
3FROM race_results;
1[secondary_label Output]
2+-------------+---------------+----------------+
3| runner_name | race_name | total_time |
4+-------------+---------------+----------------+
5| bolt | 1600_meters | 20220918000630 |
6| bolt | 5K | 20221019002231 |
7| bolt | 10K | 20221120003805 |
8| bolt | half_marathon | 20221221013904 |
9| bolt | full_marathon | 20230122032310 |
10| felix | 1600_meters | 20220918000715 |
11| felix | 5K | 20221019003050 |
12| felix | 10K | 20221120011017 |
13| felix | half_marathon | 20221221021157 |
14| felix | full_marathon | 20230122040210 |
15+-------------+---------------+----------------+
1610 rows in set (0.00 sec)
您会注意到这个输出在total_time列中相当长,难以读取,后来,我们将展示如何使用CAST函数来转换这些数据值,以便它们更清晰地读取。
现在,如果您只对每个跑者在更长的比赛中所表现的表现感兴趣,例如半场和完整马拉松,您可以查询您的数据以获取这些信息。
1SELECT runner_name, race_name, end_time - start_time AS half_full_results
2FROM race_results
3WHERE total_miles > 12;
1[secondary_label Output]
2+-------------+---------------+-------------------+
3| runner_name | race_name | half_full_results |
4+-------------+---------------+-------------------+
5| bolt | half_marathon | 20221221013904 |
6| bolt | full_marathon | 20230122032310 |
7| felix | half_marathon | 20221221021157 |
8| felix | full_marathon | 20230122040210 |
9+-------------+---------------+-------------------+
104 rows in set (0.00 sec)
在本节中,您使用SELECT语句对日期和时间进行了一些算法,并为实用目的对样本数据进行算法。
使用日期和时间函数和间隔表达式
SQL 函数通常用于处理或操纵数据,可用的函数取决于 SQL 实现程序,但大多数 SQL 实现程序允许您通过查询current_date和current_time值来查找当前日期和时间。
例如,要查找今天的日期,语法简短,仅包含SELECT语句和current_date函数,如下所示:
1SELECT current_date;
1[secondary_label Output]
2+--------------+
3| current_date |
4+--------------+
5| 2022-02-15 |
6+--------------+
71 row in set (0.00 sec)
使用相同的语法,您可以使用current_time函数找到当前时间:
1SELECT current_time;
1[secondary_label Output]
2+--------------+
3| current_time |
4+--------------+
5| 17:10:20 |
6+--------------+
71 row in set (0.00 sec)
如果您更喜欢在输出中查询日期和时间,请使用current_timestamp函数:
1SELECT current_timestamp;
1[secondary_label Output]
2+---------------------+
3| current_timestamp |
4+---------------------+
5| 2022-02-15 19:09:58 |
6+---------------------+
71 row in set (0.00 sec)
例如,假设您想知道从今天的日期开始的 11 天前的日期是什么,在这种情况下,您可以使用您之前使用的相同语法结构来查询current_date函数,然后从中提取11,以便找到 11 天前的日期:
1SELECT current_date - 11;
1[secondary_label Output]
2+-------------------+
3| current_date - 11 |
4+-------------------+
5| 20220206 |
6+-------------------+
71 row in set (0.01 sec)
正如本输出所示,11天前的current_date(本文写入时)是2022-02-06,即2022年2月6日,现在尝试运行此操作,但用current_time函数替换current_date:
1SELECT current_time - 11;
1[secondary_label Output]
2+-------------------+
3| current_time - 11 |
4+-------------------+
5| 233639 |
6+-------------------+
71 row in set (0.00 sec)
此输出显示,当您从current_time值中提取11时,它会提取11秒。 您之前使用current_date函数运行的操作将11解释为天,而不是秒。 在使用日期和时间函数时如何解释数字时,这种不一致可能会令人困惑。
INTERVAL表达式允许您查找日期或时间在特定日期或时间表达式的设置间隔之前或之后会是什么样子。
1[label Example interval expression]
2INTERVAL value unit
例如,要查找从现在起五天后的日期,您可以运行以下查询:
1SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";
此示例会找到 current_date 值,然后将间隔表达式 `INTERVAL '5' DAY' 添加到该值,从而返回从现在开始的 5 天日期:
1[secondary_label Output]
2+-------------------+
3| 5_days_from_today |
4+-------------------+
5| 2022-03-06 |
6+-------------------+
71 row in set (0.00 sec)
这比下列查询更为模糊,产生类似但不是相同的输出:
1SELECT current_date + 5 AS "5_days_from_today";
1[secondary_label Output]
2+-------------------+
3| 5_days_from_today |
4+-------------------+
5| 20220306 |
6+-------------------+
71 row in set (0.00 sec)
请注意,您还可以从日期或时间中扣除间隔,以便从指定日期值的 前中找到值:
1SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";
1[secondary_label Output]
2+--------------+
3| 7_months_ago |
4+--------------+
5| 2021-08-01 |
6+--------------+
71 row in set (0.00 sec)
您可以在INTERVAL表达式中使用的单位取决于您的DBMS选择,但大多数将有小时、分钟和秒等选项:
1SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
2current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
3current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";
1[secondary_label Output]
2+------------------+---------------+---------------------+
3| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
4+------------------+---------------+---------------------+
5| 07:51:43 | 01:46:43 | 01:52:03.000000 |
6+------------------+---------------+---------------------+
71 row in set (0.00 sec)
现在您已经了解了间隔表达式和某些日期和时间函数,继续练习使用您在第一步中插入的样本数据。
使用 CAST 和合并函数与日期和时间
从 使用日期和时间算法部分的第三个示例中回顾一下,当您运行下列查询时,将 end_time' 从 start_time' 中提取,以计算每个跑者每场比赛完成的总时间。
1SELECT runner_name, race_name, end_time - start_time
2AS total_time
3FROM race_results;
1[secondary_label Output]
2+-------------+---------------+----------------+
3| runner_name | race_name | total_time |
4+-------------+---------------+----------------+
5| bolt | 1600_meters | 20220918000630 |
6| bolt | 5K | 20221019002231 |
7| bolt | 10K | 20221120003805 |
8| bolt | half_marathon | 20221221013904 |
9| bolt | full_marathon | 20230122032310 |
10| felix | 1600_meters | 20220918000715 |
11| felix | 5K | 20221019003050 |
12| felix | 10K | 20221120011017 |
13| felix | half_marathon | 20221221021157 |
14| felix | full_marathon | 20230122040210 |
15+-------------+---------------+----------------+
1610 rows in set (0.00 sec)
由于您正在使用具有不同数据类型的两个列执行操作(‘end_time’ 包含‘TIMESTAMP’值和‘start_time’ 包含‘TIME’值),因此数据库在打印操作结果时不知道使用哪种数据类型。
为了帮助将这些数据更清晰地读取和解释,您可以使用CAST函数将这些长整数值转换为TIME数据类型。
下面的查询与上一个示例相同,但使用CAST函数将total_time列转换为time数据类型:
1SELECT runner_name, race_name, CAST(end_time - start_time AS time)
2AS total_time
3FROM race_results;
1[secondary_label Output]
2+-------------+---------------+------------+
3| runner_name | race_name | total_time |
4+-------------+---------------+------------+
5| bolt | 1600_meters | 00:06:30 |
6| bolt | 5K | 00:22:31 |
7| bolt | 10K | 00:38:05 |
8| bolt | half_marathon | 01:39:04 |
9| bolt | full_marathon | 03:23:10 |
10| felix | 1600_meters | 00:07:15 |
11| felix | 5K | 00:30:50 |
12| felix | 10K | 01:10:17 |
13| felix | half_marathon | 02:11:57 |
14| felix | full_marathon | 04:02:10 |
15+-------------+---------------+------------+
1610 rows in set (0.00 sec)
CAST在这个输出中将数据值转换为TIME,使其更易于读取和理解。
现在,让我们使用一些集合函数结合CAST函数来找到每个跑者最短、最长和总时间的结果。 首先,请查询与MIN集合函数所花费的最小(或最短)时间。 再次,您将希望使用CAST将TIMESTAMP数据值转换为TIME数据值,以获得清晰度。 请注意,当您使用像本示例这样的两个函数时,需要使用两对密码,并将总小时的计算(‘end_time - start_time’)嵌入其中一个函数中。 最后,添加一个GROUP BY(https://andsky.com/tech/tutorials/how-to-manage-sql-database-cheat-sheet#sorting-results-with-group-by-clauses)条款以根据nerrun_name列来组织这些值,以便输出呈现两个跑者比赛的结果:
1SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
2FROM race_results GROUP BY runner_name;
1[secondary_label Output]
2+-------------+----------+
3| runner_name | min_time |
4+-------------+----------+
5| bolt | 00:06:30 |
6| felix | 00:07:15 |
7+-------------+----------+
82 rows in set (0.00 sec)
这个输出显示了每个跑者最短的跑步时间,在这种情况下,Bolt的最低时间为6分钟30秒,Felix的最低时间为7分钟15秒。
然后,找出每个跑者最长的运行时间. 您可以使用与上一个查询相同的语法,但这次用MAX代替MIN:
1SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
2FROM race_results GROUP BY runner_name;
1[secondary_label Output]
2+-------------+----------+
3| runner_name | max_time |
4+-------------+----------+
5| bolt | 03:23:10 |
6| felix | 04:02:10 |
7+-------------+----------+
82 rows in set (0.00 sec)
这个结果告诉我们,博尔特最长的跑步时间总共为3小时23分钟10秒;而菲利克斯总共为4小时2分钟10秒。
现在,让我们查询一些高级别的信息,关于每个跑者花费的总时间运行. 对于这个查询,将SUM总数函数结合起来,以end_time - start_time为基础找到总数小时,然后使用CAST将这些数据值转换为TIME。
1SELECT runner_name, SUM(CAST(end_time - start_time AS time))
2AS total_hours FROM race_results GROUP BY runner_name;
1[secondary_label Output]
2+-------------+-------------+
3| runner_name | total_hours |
4+-------------+-------------+
5| bolt | 52880 |
6| felix | 76149 |
7+-------------+-------------+
82 rows in set (0.00 sec)
有趣的是,这个输出显示了MySQL的解释,这实际上是以整数计算的总时间。如果我们将这些结果读取为时间,Bolt的总时间将分为5小时,28分钟和80秒;而Felix的时间将分为7小时,61分钟和49秒。
1SELECT runner_name, SUM(CAST(end_time - start_time AS time))
2AS total_hours FROM race_results GROUP BY runner_name;
1[secondary_label Output]
2 runner_name | total_hours
3-------------+-------------
4 felix | 10:01:44
5 bolt | 06:09:20
6(2 rows)
在这种情况下,PostgreSQL 中的查询将值解释为时间,并将其计算为时间,使Felix 的结果分为总共 10 小时、1 分钟和 44 秒;而 Bolt 的查询则为 6 小时、9 分钟和 20 秒。
结论
了解如何在 SQL 中使用日期和时间在查询特定结果时有用,例如分钟、秒、小时、天、月、年;或所有这些的组合. 此外,对于日期和时间有许多可用函数,可以更容易地找到某些值,如当前日期或时间。 虽然本教程只使用 SQL 中的日期和时间的算法加和扣除,但您可以使用任何数学表达式的日期和时间值。 了解更多关于 数学表达式和汇总函数的指南,并通过您的日期和时间查询来测试它们。