获取时差并将其转换为 MySQL 中的小时数？
您可以借助timestampdiff()MySQL的方法来实现。语法如下-
语法
SELECT ABS(TIMESTAMPDIFF(HOUR,yourColumnName1,yourColumnName2)) as anyVariableName from yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table DifferenceInHours -> ( -> StartDateTime datetime, -> EndDateTime datetime -> );
使用插入命令在表中插入一些记录。查询如下-
mysql> insert into DifferenceInHours values('2018-12-20 10:00:00', '2018-12-19 12:00:00');
mysql> insert into DifferenceInHours values('2018-12-20 11:00:00', '2018-12-19 11:00:00');
mysql> insert into DifferenceInHours values('2018-12-20 10:30:00', '2018-12-19 11:00:00');使用select语句显示表中的所有记录。查询如下-
mysql> select *from DifferenceInHours;输出结果
+---------------------+---------------------+ | StartDateTime | EndDateTime | +---------------------+---------------------+ | 2018-12-20 10:00:00 | 2018-12-19 12:00:00 | | 2018-12-20 11:00:00 | 2018-12-19 11:00:00 | | 2018-12-20 10:30:00 | 2018-12-19 11:00:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec)
这是获取以小时为单位的时差的查询。查询如下-
mysql> SELECT ABS(TIMESTAMPDIFF(HOUR,StartDateTime,EndDateTime)) as Hour from DifferenceInHours;
以下是显示以小时为单位的时差的输出-
+------+ | Hour | +------+ | 22 | | 24 | | 23 | +------+ 3 rows in set (0.00 sec)