如何移除SQL时间差列中的‘0-0 0’前缀？

问题核心是：两个TIME类型值相减得到的是**时间间隔（INTERVAL）**类型，而非普通字符串，所以直接用REPLACE、RIGHT等字符串函数会因类型不匹配失败。需要先将间隔类型转换为字符串再处理前缀，或者用特定函数提取纯时间部分。

通用解决方案（跨引擎） #

先把时间差转换为字符串，再移除固定前缀0-0 0 ：

方案1：替换前缀 #

SELECT CAST(Left(time, 8) AS time) AS time, 
       coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
       -- 先转字符串再替换冗余前缀
       TRIM(REPLACE(
           CAST(
               CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
               OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) 
           AS VARCHAR), 
           '0-0 0 ', 
           ''
       )) AS time_difference
FROM orders.sheet1 
WHERE message_type IN ('ENTER', 'DELETE') 
ORDER BY order_id

方案2：固定位置截取 #

如果前缀0-0 0 的长度固定（共6个字符），可以直接从第7位开始截取：

SELECT CAST(Left(time, 8) AS time) AS time, 
       coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
       SUBSTRING(
           CAST(
               CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
               OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) 
           AS VARCHAR), 
           7
       ) AS time_difference
FROM orders.sheet1 
WHERE message_type IN ('ENTER', 'DELETE') 
ORDER BY order_id

针对特定SQL引擎的优化方案 #

PostgreSQL #

用TO_CHAR直接格式化时间间隔，只保留时分秒：

SELECT CAST(Left(time, 8) AS time) AS time, 
       coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
       TO_CHAR(
           CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
           OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time),
           'HH24:MI:SS'
       ) AS time_difference
FROM orders.sheet1 
WHERE message_type IN ('ENTER', 'DELETE') 
ORDER BY order_id

SQL Server #

通过DATEPART提取时分秒后拼接，避免字符串转换的前缀问题：

WITH order_time_cte AS (
    SELECT CAST(Left(time, 8) AS time) AS time, 
           coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
           CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
           OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) AS raw_time_diff
    FROM orders.sheet1 
    WHERE message_type IN ('ENTER', 'DELETE')
)
SELECT time, 
       order_id,
       CONCAT(
           DATEPART(HOUR, raw_time_diff), ':',
           RIGHT('0' + CAST(DATEPART(MINUTE, raw_time_diff) AS VARCHAR), 2), ':',
           RIGHT('0' + CAST(DATEPART(SECOND, raw_time_diff) AS VARCHAR), 2)
       ) AS time_difference
FROM order_time_cte
ORDER BY order_id

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