如何在Toad for Oracle中将计算出的时间差转换为小数形式？

我编写了以下查询语句，用于计算某一批次（Lot/Batch）的最晚检验时间与最早检验时间的差值（自定义列名为Duration）：

SELECT LOT_ID, 
    MAX(INSPECTION_TIME), 
    MIN(INSPECTION_TIME), 
    MAX(INSPECTION_TIME) - MIN(INSPECTION_TIME) AS Duration
FROM table_name
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID

查询结果如下：

我希望在结果中添加一个额外列，以小数形式显示该时间差的小时数并保留三位小数，最终期望结果如下：

实现方法取决于你使用的数据库类型，以下是主流数据库的具体处理语句：

MySQL/MariaDB #

通过TIMESTAMPDIFF计算时间差的总秒数，再转换为小时并保留三位小数：

SELECT LOT_ID, 
    MAX(INSPECTION_TIME) AS MAX_INSPECTION_TIME,
    MIN(INSPECTION_TIME) AS MIN_INSPECTION_TIME,
    TIMEDIFF(MAX(INSPECTION_TIME), MIN(INSPECTION_TIME)) AS Duration,
    ROUND(TIMESTAMPDIFF(SECOND, MIN(INSPECTION_TIME), MAX(INSPECTION_TIME)) / 3600, 3) AS ROUNDED
FROM table_name
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID

PostgreSQL #

使用EXTRACT提取时间差的纪元秒数，换算为小时后保留三位小数：

SELECT LOT_ID, 
    MAX(INSPECTION_TIME) AS MAX_INSPECTION_TIME,
    MIN(INSPECTION_TIME) AS MIN_INSPECTION_TIME,
    MAX(INSPECTION_TIME) - MIN(INSPECTION_TIME) AS Duration,
    ROUND(EXTRACT(EPOCH FROM (MAX(INSPECTION_TIME) - MIN(INSPECTION_TIME))) / 3600, 3) AS ROUNDED
FROM table_name
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID

SQL Server #

用DATEDIFF计算总秒数，转换为小时后通过ROUND保留三位小数：

SELECT LOT_ID, 
    MAX(INSPECTION_TIME) AS MAX_INSPECTION_TIME,
    MIN(INSPECTION_TIME) AS MIN_INSPECTION_TIME,
    CONVERT(VARCHAR, DATEADD(SECOND, DATEDIFF(SECOND, MIN(INSPECTION_TIME), MAX(INSPECTION_TIME)), 0), 121) AS Duration,
    ROUND(DATEDIFF(SECOND, MIN(INSPECTION_TIME), MAX(INSPECTION_TIME)) / 3600.0, 3) AS ROUNDED
FROM table_name
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID

内容的提问来源于stack exchange，提问作者user22060295