Oracle转PostgreSQL：实现hh:mm:ss格式时间差函数求助

针对你提供的Oracle时间差计算代码，以下是适配PostgreSQL的版本，同时优化了逻辑可读性：

基础版（与原Oracle逻辑一致，仅显示当天内的小时差） #

RIGHT('0' || CAST(
  EXTRACT(HOUR FROM (
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({1},1,2) || ':' || SUBSTRING({1},3,2) || ':' || SUBSTRING({1},5,2), 'YYYY-MM-DD"T"HH24:MI:SS') -
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({2},1,2) || ':' || SUBSTRING({2},3,2) || ':' || SUBSTRING({2},5,2), 'YYYY-MM-DD"T"HH24:MI:SS')
  )) AS VARCHAR(10)), 2) || ':' ||
RIGHT('0' || CAST(
  EXTRACT(MINUTE FROM (
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({1},1,2) || ':' || SUBSTRING({1},3,2) || ':' || SUBSTRING({1},5,2), 'YYYY-MM-DD"T"HH24:MI:SS') -
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({2},1,2) || ':' || SUBSTRING({2},3,2) || ':' || SUBSTRING({2},5,2), 'YYYY-MM-DD"T"HH24:MI:SS')
  )) AS VARCHAR(10)), 2) || ':' ||
RIGHT('0' || CAST(
  EXTRACT(SECOND FROM (
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({1},1,2) || ':' || SUBSTRING({1},3,2) || ':' || SUBSTRING({1},5,2), 'YYYY-MM-DD"T"HH24:MI:SS') -
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({2},1,2) || ':' || SUBSTRING({2},3,2) || ':' || SUBSTRING({2},5,2), 'YYYY-MM-DD"T"HH24:MI:SS')
  )) AS VARCHAR(10)), 2)

优化精简版（减少重复计算） #

WITH time_diff AS (
  SELECT 
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({1},1,2) || ':' || SUBSTRING({1},3,2) || ':' || SUBSTRING({1},5,2), 'YYYY-MM-DD"T"HH24:MI:SS') -
    TO_TIMESTAMP('1970-01-01T' || SUBSTRING({2},1,2) || ':' || SUBSTRING({2},3,2) || ':' || SUBSTRING({2},5,2), 'YYYY-MM-DD"T"HH24:MI:SS') AS interval_diff
)
SELECT 
  RIGHT('0' || CAST(EXTRACT(HOUR FROM interval_diff) AS VARCHAR(10)), 2) || ':' ||
  RIGHT('0' || CAST(EXTRACT(MINUTE FROM interval_diff) AS VARCHAR(10)), 2) || ':' ||
  RIGHT('0' || CAST(EXTRACT(SECOND FROM interval_diff) AS VARCHAR(10)), 2)
FROM time_diff;

支持跨天累计小时版（如30小时显示为30:XX:XX） #

如果时间差可能超过24小时，原逻辑会只显示小时余数，这个版本会计算累计小时：

WITH total_seconds AS (
  SELECT 
    EXTRACT(EPOCH FROM (
      TO_TIMESTAMP('1970-01-01T' || SUBSTRING({1},1,2) || ':' || SUBSTRING({1},3,2) || ':' || SUBSTRING({1},5,2), 'YYYY-MM-DD"T"HH24:MI:SS') -
      TO_TIMESTAMP('1970-01-01T' || SUBSTRING({2},1,2) || ':' || SUBSTRING({2},3,2) || ':' || SUBSTRING({2},5,2), 'YYYY-MM-DD"T"HH24:MI:SS')
    )) AS secs
)
SELECT 
  RIGHT('0' || CAST(secs / 3600 AS VARCHAR(10)), 2) || ':' ||
  RIGHT('0' || CAST((secs % 3600) / 60 AS VARCHAR(10)), 2) || ':' ||
  RIGHT('0' || CAST(secs % 60 AS VARCHAR(10)), 2)
FROM total_seconds;

关键改动说明 #

• 替换Oracle的DATEDIFF：PostgreSQL没有该函数，改为直接计算两个TIMESTAMP的差值得到INTERVAL，再用EXTRACT提取时分秒分量
• 字符串拼接：用PostgreSQL标准的||替代Oracle的+
• 时间转换：使用TO_TIMESTAMP并指定明确格式串'YYYY-MM-DD"T"HH24:MI:SS'，避免隐式转换的歧义
• 移除冗余：优化版通过CTE只计算一次时间差，减少重复运算

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