Oracle interval '1' YEAR 今天报错了

  • 2.28日,客户微信公众号更新账单列表功能:业主群里反馈用户查到的账单记录不是自己的,经验证和推测是同事写死了用户编号。好吧!!!在家办公,有点马虎,表示理解吧。
  • 2.29日,星期六,懒觉还没睡醒,业主群里反馈账单查不到了。I'm Angry,直接把问题截图转发到了小组沟通群里。

转过头来一想,不对呀,昨天更新后是可以正常使用的呀。这家伙也不会这么用工,加班又给更新一版。感觉不对,查日志。

API网关日志

账单列表是默认加载近一年的,也没有传递时间相关的信息呀,怎么就指定的月份日期无效呢。直接在后台执行对应SQL,还是报错。

ORA-01839: 指定月份的日期无效
01839. 00000 -  "date not valid for month specified"

看来是SQL语句的问题,但是接口最近就没有更新呀。最终锁定在

sysdate - interval '1' YEAR
--当然这个问题必须是在特定日期才会出现的,比如
select TO_DATE('2020-02-29','yyyy-MM-dd') - interval '1' YEAR from dual

经网上查询,原来这个问题是因为闰年29号以及oracel的interval处理机制造成。

Query containing SYSDATE - INTERVAL '1' YEAR fails for today's date(29th February 2016)

这里先说替代写法:

select add_months(TO_DATE('2020-02-29','yyyy-MM-dd'),-12) from dual

这里有问题的说明:

It might be disappointing, but it is to be expected. [It is mentioned in the documentation](https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#i48042):
When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error. For example, the next two statements return errors:
SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1') FROM DUAL;
  SELECT TO_DATE('29-FEB-2004','DD-MON-YYYY') + TO_YMINTERVAL('1-0') FROM DUAL; 
The first fails because adding one month to a 31-day month would result in September 31, which is not a valid date. The second fails because adding one year to a date that exists only every four years is not valid. 

大概意思是:

Oracle的官方文档中已经说了:interval计算返回一个datetime值,返回值必须是一个准确的datetime值,否则返回错误。

说白了,interval计算后,Oracle不会做转换,比如第一种情况,没有31号,不会帮你转到次月1号;第二种情况,没有29号,不会帮你转到次月1号或本月20号。因为这样不准确。

最后感叹一下,自己遇到了破解了一个4年一遇的bug!!!

posted @ 2020-03-03 21:37  我打农村来  阅读(542)  评论(0编辑  收藏