Oracle 18c中如何计算两个Timestamp差值并仅提取小时和分钟

当你直接对两个Timestamp做减法时，得到的是INTERVAL DAY TO SECOND类型，要正确提取小时和分钟，需要注意时区统一和跨天差值的处理，以下是可行的解决方法：

问题根源 #

1. 你使用TO_TIMESTAMP解析带时区的字符串，得到的是无时区的TIMESTAMP类型，和带时区的SYSTIMESTAMP运算时，Oracle会自动转换为数据库时区，可能和你输入的+05:30时区不符，导致差值计算错误。
2. 直接用EXTRACT提取小时时，不会自动把天数转换为小时，跨天的差值会漏掉这部分。

正确实现方式 #

方式1：提取总小时和分钟（含跨天） #

先统一时区计算差值，再把天数转成小时后和提取的小时相加，同时提取分钟：

WITH calc_diff AS (
    SELECT 
        -- 用TO_TIMESTAMP_TZ明确解析带时区的时间字符串
        TO_TIMESTAMP_TZ('29-09-22 2:27:48.696000000 PM +05:30', 'DD-MM-RR HH:MI:SS.FF AM TZR') 
        - SYSTIMESTAMP AS diff_interval
    FROM dual
)
SELECT 
    -- 把天数转成小时，加上提取的小时，得到总小时数
    EXTRACT(DAY FROM diff_interval)*24 + EXTRACT(HOUR FROM diff_interval) AS total_hours,
    EXTRACT(MINUTE FROM diff_interval) AS total_minutes
FROM calc_diff;

方式2：格式化为“小时:分钟”形式 #

如果需要直接得到HH24:MI格式的结果，可以把差值加到一个基准日期上，再用TO_CHAR提取：

SELECT 
    TO_CHAR(
        TRUNC(SYSDATE) + (TO_TIMESTAMP_TZ('29-09-22 2:27:48.696000000 PM +05:30', 'DD-MM-RR HH:MI:SS.FF AM TZR') - SYSTIMESTAMP),
        'HH24:MI'
    ) AS hour_minute
FROM dual;

关键说明 #

• 必须使用TO_TIMESTAMP_TZ而非TO_TIMESTAMP来解析带时区的时间字符串，确保时区信息被正确识别，避免计算偏差。
• 当差值超过1天时，EXTRACT(HOUR)只会提取当天的小时数，必须加上天数*24才能得到总小时数。

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