卡卡网

我们在MySQL计算两日期时间差时，往往是使用算术表达式来计算直接获得结果，但当两字段数据类型不同时，获得的结果是一串难以理解的数字。为此，我们需要更新这种写法，要以获得易理解易阅读和易用结果为目标，这是本文的初衷，而一个很简单的CAST()函数，就能满足我们的要求。

介绍

CAST()函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法：

CAST (expression AS data_type)

• expression：任何有效的SQServer表达式。
• AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
• data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

示例

运行以下查询，以start_time减去end_time计算小时数（提示：end_time数据类型是TIMESTAMP，start_time数据类型是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)

可以看到，输出包含一列很长的输出，该输出跟随表中设置的TIMESTAMP数据类型。

因为你正在使用具有不同数据类型（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 秒；而 Felix 一共是 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;

输出：

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 秒。这表明它是以整数而不是时间计算的。如果在不同的 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 秒；而 Bolt 的时间是 6 小时 9 分 20 秒。这是一个示例，说明各种 DBMS 以不同方式解释数据值，即使它使用相同的查询和数据集。

总结

本教程仅对 SQL 中的日期和时间使用了加法和减法运算，但你可以将日期和时间值与任何数学表达式一起使用。从我们的数学表达式和聚合函数指南中了解更多信息，并在你的日期和时间查询中试用它们。

标签: MySQL