PostgreSQL计算日期时间分摊比例时3月返回异常结果问题求助

异常产生原因 #

1. 先拆分你的计算公式各部分逻辑：

• 分子(iot.DATE_FIN - iot.DATE_DEBUT)::double precision：PostgreSQL中两个DATE类型相减得到的是不含结束日的间隔天数，你的3月测试案例中2020-03-31减2020-03-01得到30，这部分计算符合预期。
• 分母extract (day from iot.DATE_FIN - date_trunc('month', iot.DATE_DEBUT))存在两个问题：
  • 隐式类型转换与时区偏移问题：date_trunc返回的是TIMESTAMP类型，和DATE类型做减法得到的是INTERVAL类型。如果你的数据库时区不是UTC，date_trunc处理2020-03-01时会因时区转换偏移到2020年2月29日（2020年是闰年，2月有29天），最终2020-03-31减去这个偏移后的月初值，extract(day from interval)只会取整数天部分的29，忽略不满1天的时长，导致分母变成29，30/29≈1.0344827586206897，就是你拿到的异常结果。
  • 原始逻辑设计错误：你的需求是计算时间段在当月的分摊比例，分母应该取当月总天数，你写的逻辑是取结束日期到月初的间隔天数，即使没有时区问题，当结束日期不是当月最后一天时，分母计算结果也不符合需求。

修复方案 #

替换分母为当月总天数的正确计算逻辑即可，如果需要统计包含首尾日期的占比，分子需要额外加1，修正后的完整SQL如下：

select iot.*,
       (iot.DATE_FIN - iot.DATE_DEBUT + 1)::double precision 
       / NULLIF(extract(day from (date_trunc('month', iot.DATE_DEBUT) + INTERVAL '1 month' - INTERVAL '1 day')), 0::double precision) as prorata
from (
   select DATE '2020-03-01' as DATE_DEBUT, DATE '2020-03-31' as DATE_FIN
) iot

运行后会返回预期结果1。

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