前几天有个朋友遇到一个问题,在做日期类型数据的运算的时候出现了‘0000-00-00’的结果,不得其解。你是否遇到过同样的问题呢?这样一个并不存在的时间点,难道是因为数据库系统穿越了?
在使用ogg同步数据到备库的时候,报ORA-01850的错误,通过logdump发现了很多类似的SQL报错,选取其中一个如下:
select to_char(a.station_time) from sfis1.r_vip_log a where id=486270420;
结果显示:0000-00-00
因为显示结果年份为0,属于不合法的时间格式,因此报错。
说明:在Oracle中,date类型的数据的取值范围是从-4712/12/31到9999/12/31之间,年份不能为0。并且从12.1开始,对于小时和分钟做了更精确的判断。比如来看上面ORA-01850的错误为:
首先尝试不做类型转换,直接查询:
select a.station_time from sfis1.r_vip_log a where id=486270420;
此时输出时间正常。(要说明一点的是,这里输出的时间仍然不对,但涉及的问题跟本文无关,并且是正常的日期格式,所以此处不做深入探究)
这是什么原因?遇到这种问题,我们可能首先会猜,是不是遭遇了bug,那么首先来看数据库版本,是10204,果然是比较低的版本,因此初步猜测是10g的bug。
但这一猜测很快就被否定。
为了验证这是一个bug,Google了一下发现的确有人遭遇类似的问题,我参考一篇文章做了以下测试。(原文链接见:http://www.hellodba.com/reader.php?ID=95&lang=CN)
在10204,11204,12201三个版本上分别做了如下测试:
1、当日期类型做减法,刚好减完为0 的时候:
select to_date('0001-01-01', 'yyyy-mm-dd')-365 from dual;
2、减完大于0,但减法使得年份为0
select to_date('0001-01-01', 'yyyy-mm-dd')-360 from dual;
3、减完小于0
select to_date('0001/01/01', yyyy/mm/dd') - 900 from dual;
4、查询年份为0时
select date '0000-11-22' from dual;
5、年份小于0时
select date '-4712-11-11' +15 from dual;
6、非闰年遇到2.29
select date '1500-2-28' +2 from dual;
select date '1500-2-28' +1 f