Oracle中如何将时间间隔转换为纯天数值

• 需求：获取状态更新时间点之间的时间差值
• 初始实现：通过lag()窗口函数取同Billetnum分组内按时间排序的上一条记录时间戳，初始SQL如下：

SELECT RecordID,
lag(timestamp) OVER (PARTITION BY Billetnum ORDER BY timestamp)
AS PriorStamp
FROM Tracker;

• 待解决问题：直接计算时间差返回的是「天数, 时:分:秒」格式的间隔值，需要仅提取时间间隔对应的纯天数值。

你当前的SQL只完成了上一个时间点的取值，还没有做差值计算，直接在现有逻辑基础上调用对应数据库的日期差函数，即可直接返回纯天数值，不需要先拿到带时分秒的间隔结果再做字符串截取，不同数据库的写法如下：

• MySQL：使用DATEDIFF()函数，返回值为两个日期的自然天数差，自动忽略时分秒维度：

SELECT 
  RecordID,
  DATEDIFF(
    timestamp,
    lag(timestamp) OVER (PARTITION BY Billetnum ORDER BY timestamp)
  ) AS IntervalDays
FROM Tracker;

• PostgreSQL：两个timestamp类型字段直接相减得到interval类型间隔，按需选择计算规则：

-- 取自然日差，不满1天的部分直接舍弃
SELECT 
  RecordID,
  DATE_PART(
    'day',
    timestamp - lag(timestamp) OVER (PARTITION BY Billetnum ORDER BY timestamp)
  ) AS IntervalDays
FROM Tracker;

-- 按24小时为1天折算总天数，不满24小时返回小数，可套ROUND函数做取整
SELECT 
  RecordID,
  EXTRACT(EPOCH FROM (
    timestamp - lag(timestamp) OVER (PARTITION BY Billetnum ORDER BY timestamp)
  )) / 86400 AS IntervalDays
FROM Tracker;

• SQL Server：使用DATEDIFF函数指定day维度计算，返回跨自然日的天数差：

SELECT 
  RecordID,
  DATEDIFF(
    day,
    lag(timestamp) OVER (PARTITION BY Billetnum ORDER BY timestamp),
    timestamp
  ) AS IntervalDays
FROM Tracker;

提示：如果时间字段带时区属性，计算前需将两个时间统一到相同时区，避免时区偏移导致天数计算错误。优先使用数据库原生日期差函数计算，比字符串截取的方式性能更高，也不会出现格式匹配异常。

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