如何在PostgreSQL中将时间间隔格式转为天数小数？

不同SQL数据库的最优实现方式如下：

• PostgreSQL
  利用EXTRACT(EPOCH FROM ...)获取时间间隔的总秒数，再除以一天的秒数（86400）得到小数天数：

  select time1, time2, EXTRACT(EPOCH FROM (time1 - time2)) / 86400 as time_discrep_days
  from main_file
  order by time1;
  

• MySQL
  使用TIMESTAMPDIFF函数计算两个时间的秒数差，再转换为天：

  select time1, time2, TIMESTAMPDIFF(SECOND, time2, time1) / 86400 as time_discrep_days
  from main_file
  order by time1;
  

• SQL Server
  通过DATEDIFF获取秒数差，除以86400.0（用浮点数避免整数截断）：

  select time1, time2, DATEDIFF(SECOND, time2, time1) / 86400.0 as time_discrep_days
  from main_file
  order by time1;
  

如果你的现有方案是基于总时长转秒数再除以86400的思路，那这已经是行业内的常规最优方案——这种方式能保证精度，且适配绝大多数场景。

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