数据库时间出现'0000/00/00',难道我穿越了?

简介:

前几天有个朋友遇到一个问题,在做日期类型数据的运算的时候出现了‘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的新特性,也仍有很多待我们去发现。热爱学习的你,欢迎跟我们一起来踩坑。


本文出自数据和云公众号,原文链接


相关文章
|
SQL 存储 算法
数据库挖祖坟系列-优化器设计探索穿越之旅
前沿引用来自百度百科的话术:在数据库技术发展历史上,1970 年是发生伟大转折的一年,因为这一年的6月,IBM的圣约瑟研究实验室的高级研究员Edgar Frank Codd在Communications of ACM 上发表了《A Relational Model of Data for Large Shared Data Banks》。ACM 后来在1983 年把这篇论文列为从1958年以来的2
214 0
数据库挖祖坟系列-优化器设计探索穿越之旅
|
18天前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
2月前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
|
18天前
|
存储 关系型数据库 MySQL
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
|
2月前
|
关系型数据库 MySQL 数据库连接
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
452 82
|
25天前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象,介绍多种负载均衡算法及故障排除步骤,包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法,如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。
|
1月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
1月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
4月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
2月前
|
消息中间件 缓存 NoSQL
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)

热门文章

最新文章