前几天有个朋友遇到一个问题，在做日期类型数据的运算的时候出现了‘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 from dual;
7、闰年遇到2.29
select date '1600-2-28' +1 from dual;
第一次测试的结果如下：
结果并不是预期的，有很多奇奇怪怪的输出（当时我深信他们是奇奇怪怪的），并且没有任何一种情况出现全零。这就奇怪了，再次猜测难道那篇帖子是9i 的？
这些输出在我看来没有任何规律可以总结，甚至说，在10g，11g能够正常输出的语句在12.2中不能正常输出而报错的时候，我认为这是非常不科学的。
当我们认定一件事情的时候，如果这件事情不是对的，那么很可能一直把我们带到坑里而不自知。很显然之前关于版本的猜测被推翻了。因为10g中并没有想象中出现全零的状况，而其他的结果虽然可疑，但我并没看出来任何规律。
柳暗花明又一村发生在不久之后。
有朋友看到我的测试结果，表达自己在11g中的测试结果不一样。通过他发的截图，的确又一次出现了全零的错误结果。
也就是说10g中很可能会全零，11g中也可能会。但为什么我没有遇到呢。看着两个全零结果的截图，突然发现两者都是通过plsql developer连接数据库的。这会不会有关系。此时恰好有高人指点，提到终端的显示问题。
之后杨长老对我之前的结果做了反馈，首先对于前两条SQL的输出，我认为是12.2的输出不合理，理由是做了减法反而得到的结果比被减数还大，这不科学。
事实上，对于日期类型，Oracle是可以区分正负的，正数表示公元后，负数表述公元前。只是因为我们在常用的计算中都用到的是正数，所以默认显示的是正数。如果需要显示负数，需要对时间格式加以限制。
接下来我们对以上的测试SQL做逐一的分析。
对于前两条SQL：
我们最初得到的结果如下：
很显然，一眼看去，12.2的输出年份不合理。但如果考虑正负的话，情况就不一样。
我们来测试，首先修改日期显示格式，使其能够显示日期类型的正负。
SQL> alter session set nls_date_format='syyyy-mm-dd hh24:mi:ss';
Session altered.
再次在12.2中执行前两条语句，结果如下：
SQL> select to_date('0001-01-01', 'syyyy-mm-dd')-365 from dual;
TO_DATE('0001-01-01'
--------------------
-0001-01-02 00:00:00
SQL> select to_date('0001-01-01', 'syyyy-mm-dd')-360 from dual;
TO_DATE('0001-01-01'
--------------------
-0001-01-07 00:00:00
我们发现，结果为负数，也就是说，计算结果为公元前。并没有问题，只是之前对于时间格式的设置导致数据的正负没有显示出来。
第三条很好理解：本身就不存在为0的年份，因此Oracle会直接报错。
再次查看错误码，发现的确是12.1开始出现的。
结论1：在12.2中以SQLplus登录数据库，会识别日期类型中数据的取值范围，而11g和10g的版本做了普通的运算而未校验结果。
要说明一点的是，这个改变不是12.2是新特性，根据01850和01841错误，判断应该是从12.1就开始有了新的校验机制。因为我手头没有12.1的测试环境，有疑问的同学可以自己再验证一下。
第四条和第五条很有意思。
从逻辑上来讲，1500和1000并不属于闰年，因此2月28加上两天结果应该是3月2日，但此时两个结果都很明显算进去了不存在的2月29日。
这是怎么回事？之前我们对于闰年，闰月，闰秒有过很多的探讨，如果说Oracle不能识别那也不科学，但此时为什么没有显示。
首先在杨老师建议下，我换了一组数据做了测试，将非闰年的选择时间改为1900年。
SQL> select date '1900-2-28' +2 from dual;
DATE'1900-2-28'+2
-------------------
1900-03-02 00:00:00
SQL> select date '1900-2-28' +1 from dual;
DATE'1900-2-28'+1
-------------------
1900-03-01 00:00:00
很显然，这次的结果考虑了闰年的问题。那说明，的确是有一个时间点的划分，在此之前和之后，Oracle的处理方式是不一样的。
通过以下的SQL可以验证：
SQL> select to_date(to_char(rownum * 100) || '-2-28', 'yyyy-mm-dd') + 2 from dual connect by rownum <= 30;
TO_DATE(TO_CHAR(ROWN
--------------------
0100-03-01 00:00:00
0200-03-01 00:00:00
0300-03-01 00:00:00
0400-03-01 00:00:00
0500-03-01 00:00:00
0600-03-01 00:00:00
0700-03-01 00:00:00
0800-03-01 00:00:00
0900-03-01 00:00:00
1000-03-01 00:00:00
1100-03-01 00:00:00
1200-03-01 00:00:00
1300-03-01 00:00:00
1400-03-01 00:00:00
1500-03-01 00:00:00
1600-03-01 00:00:00
1700-03-02 00:00:00
1800-03-02 00:00:00
1900-03-02 00:00:00
2000-03-01 00:00:00
2100-03-02 00:00:00
2200-03-02 00:00:00
2300-03-02 00:00:00
2400-03-01 00:00:00
2500-03-02 00:00:00
2600-03-02 00:00:00
2700-03-02 00:00:00
2800-03-01 00:00:00
2900-03-02 00:00:00
3000-03-02 00:00:00
我们看到，对于整百的非闰年年份，做相同的计算，在1600年前后的结果是不一样的。1600之后，才开始识别到底有没有2月29这个日期，之前的年份不做校验。
为什么会这样？
这里跟大家科普一下：
公历是根据罗马人的"儒略历"改编而得。由于当时没有了解到每年要多算出0.0078天的问题，从公元前46年，到16世纪，一共累计多出了10天。为此，当时的教皇格列高利十三世，将1582年10月5日人为规定为10月15日。并开始了新闰年规定。即规定公历年份是整百数的，必须是400的倍数才是闰年，不是400的倍数的就是平年。
简单来说，就是1582之后才有闰年计算标准，因此在上面的测试中，由于1600是闰年，从1700开始的结果是正确的。
结论二：由于闰年的计算标准始于1582年，Oracle对于这个时间点前后的数据处理方式不一样，1582之前的数据，不做闰年校验，默认2月29天；1582之后才开始校验。
当然Oracle在这里也是有点偷懒，认为在1582之前没有这种计算标准，就不做校验是不对的。
接下来的两条SQL，
对于年份小于0的，或者运算之后的年份小于0的，同样是负数，也就是公元前的计时方式。我们通过显示正负号再次验证：
SQL> alter session set nls_date_format='syyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select to_date('0001/01/01','syyyy/mm/dd') - 900 from dual;
TO_DATE('0001/01/01'
--------------------
-0002-07-16 00:00:00
SQL> select date'-4712-11-11'+15 from dual;
DATE'-4712-11-11'+15
--------------------
-4712-11-26 00:00:00
全为负值，也就是公元前，经计算，逻辑合理。
至此，关于日期的正负告一段落。那么，为什么有些环境下能测出全零的结果呢？
之前我们说，可能是终端的显示问题。事实上，更确切的说，不只是显示，更是工具与Oracle在进行衔接过程中的机制问题。
通过plsql developer等工具连接数据库的时候，事实上并不只是简单地连接，读取请求和返回结果，内部有很多复杂的机制。Oracle是用c语言写的，为了跟Oracle内部更好地衔接，这些外部的工具很可能做了很多c的接口，这些接口对于数据的操作不止于读取和传递，这就是为什么很多时候我们使用不同的工具很可能同样的SQL会有不一样的结果。
回到最初的问题：
select to_char(a.station_time) from sfis1.r_vip_log a where id=486270420;
使用to_char函数做转换的时候，导致出现全零，也可以看出在plsql developer上调用to_char函数的时候，对于数据格式和类型的转换机制不完全等同于Oracle的转换机制。这很可能也是因为工具的接口函数导致。
这样的例子还有很多，比如当我们用SQLplus连接数据库查询一张表的时候，会直接返回所有的行，但plsql developer则可能会先显示一部分，其他的你可以根据需求展开。也有在做逻辑导入导出的时候，有一些表和索引的创建出现问题，直接在SQLplus执行出错，而使用plsql developer则可以。
具体的区别如果有人感兴趣，可以做更深入的学习。我们在这里想跟大家强调的是，当你使用第三方工具操作数据库的时候，千万要谨慎，我们并不知道这些工具里到底埋了多少坑。
所以，至此你是否也有豁然开朗的感觉？
最后，关于文中提到的黄玮老师的帖子，猜想很可能是比较早期的版本，或者当时的一些工具的不完善导致。当然归根到底，我们看到Oracle在12c之前，通过运算仍然可以得到年份为0的情况，虽然正常显示了，但由于是不合法的数据，这些数据在需要使用比如导入导出，计算或者转换的时候就可能出错。
Oracle一直在不断地完善和进步，关于12c的新特性，也仍有很多待我们去发现。热爱学习的你，欢迎跟我们一起来踩坑。
本文出自数据和云公众号，原文链接
