PostgreSQL中从时间间隔提取月份数返回0的问题排查

这是个很容易踩的坑：DATE_PART('month', interval)的作用是提取时间间隔（interval）对象里的“月份”字段值，而不是计算两个日期之间的总月份跨度。

举个直观的例子：如果两个日期的间隔是27天（比如'2018-03-23'::timestamp - '2018-02-24'::timestamp），得到的interval是'27 days'——这个间隔里没有完整的月份单元，所以DATE_PART('month', ...)自然返回0。

不过你给出的示例日期（2018-05-31 减 2018-02-24）理论上应该得到'3 mons 7 days'的interval，这时候DATE_PART('month', ...)应该返回3才对。如果实际返回0，大概率是你输入的日期有误（比如把前后日期写反了？或者日期格式不规范？），或者时区设置导致timestamp转换后日期发生了偏移。

如果你想计算两个日期的总月份差 #

如果你的目标是得到两个日期之间的月份跨度（比如不管天数，只算月份差），那DATE_PART('month', ...)不是正确的工具，推荐两种实用方法：

方法1：用age()函数计算完整年月差 #

age()函数会返回包含年、月、日的完整间隔，我们可以把年转换成月份再加上月数，得到总月份差：

SELECT 
  EXTRACT(year FROM age('2018-05-31'::timestamp, '2018-02-24'::timestamp)) * 12 +
  EXTRACT(month FROM age('2018-05-31'::timestamp, '2018-02-24'::timestamp));

这个语句会返回3，正好是示例日期的月份跨度。

方法2：截断到月份后计算间隔 #

如果只想按“整月”计算（比如2018-03-24到2018-02-24算1个月，2018-03-23到2018-02-24算0个月），可以先把日期截断到月份，再计算间隔：

SELECT DATE_PART('month', 
  DATE_TRUNC('month', '2018-05-31'::timestamp) - 
  DATE_TRUNC('month', '2018-02-24'::timestamp)
);

这个也会返回3，因为截断后是2018-05-01减2018-02-01，正好是3个月的间隔。

总结 #

你看到返回0的核心原因是：你计算的时间间隔里没有完整的月份分量，而DATE_PART('month', ...)只是提取这个分量，不是计算总月份数。如果是要算月份跨度，就用上面的两种方法之一。

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