Oracle中如何计算两个varchar2类型时间戳的差值

要计算emp表中dtime_details列（VARCHAR2类型，存储格式为YYYY-MM-DD HH24:MI:SS）的两个时间戳差值并取整为小时，核心是先将字符串转换为时间类型，再计算差值。

1. 计算两个特定时间字符串的差值 #

直接转换字符串为时间类型后计算：

SELECT 
    FLOOR(
        (TO_TIMESTAMP('2021-03-01 17:40:00', 'YYYY-MM-DD HH24:MI:SS') 
        - TO_TIMESTAMP('2021-03-01 08:36:00', 'YYYY-MM-DD HH24:MI:SS')) 
        * 24
    ) AS hour_diff
FROM DUAL;

也可以用TO_DATE函数替代TO_TIMESTAMP，效果一致：

SELECT 
    FLOOR(
        (TO_DATE('2021-03-01 17:40:00', 'YYYY-MM-DD HH24:MI:SS') 
        - TO_DATE('2021-03-01 08:36:00', 'YYYY-MM-DD HH24:MI:SS')) 
        * 24
    ) AS hour_diff
FROM DUAL;

2. 计算表中两行数据的时间差值 #

如果是表中不同行的时间（比如同一员工的上下班时间），可以用聚合函数取最大/最小值计算：

SELECT 
    FLOOR(
        (MAX(TO_TIMESTAMP(dtime_details, 'YYYY-MM-DD HH24:MI:SS')) 
        - MIN(TO_TIMESTAMP(dtime_details, 'YYYY-MM-DD HH24:MI:SS'))) 
        * 24
    ) AS hour_diff
FROM emp
-- 这里根据实际条件过滤出需要计算的两行数据
WHERE emp_id = '123';

关键说明 #

• TO_TIMESTAMP/TO_DATE的格式掩码'YYYY-MM-DD HH24:MI:SS'必须和dtime_details列存储的字符串格式完全匹配，否则会报错
• 时间类型相减后，TO_DATE返回天数，乘以24转为小时；TO_TIMESTAMP返回时间间隔，乘以24同样可转为小时数值
• FLOOR函数用于取整数部分，得到你需要的9小时（实际差值为9小时4分，取整后符合预期）

优化建议 #

如果后续频繁需要进行时间计算，建议将dtime_details列的数据类型修改为TIMESTAMP或DATE，既避免每次查询的转换开销，又能保证存储数据的合法性（防止存入非时间格式的字符串）。

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