一个DATE数据类型的检索

今天快下班时，兄弟团队过来问了个问题，一张表中的DATE类型字段在PLSQL-Developer中检索的时候，出现这种现象，如下所示，有记录存储的是"2019-01-01"，即不带时间，有记录存储的是"2019-01-01 23:59:59"，即带了时间，如果想找出所有这种不带时间的记录，并对其进行更新，应该如何操作？

640?wx_fmt=png

首先，这存在个误区，有时候认为DATE类型存储的就是“日期”，TIMESTAMP类型存储的是“日期和时间”。在《SQL Language Reference》中对DATE数据类型进行了说明，明确指出DATE数据类型存储的是“date”日期和“time”时间，DATE数据类型都有自己的相关属性，对每个DATE类型的值，都会存储年、月、日、时、分和秒，换句话说，无论你是否指定，他都会存储这些，

The DATE data type stores date and time information. Although date and time information can be represented in both character and number data types, the DATE data type has special associated properties. For each DATE value, Oracle stores the following information: year, month, day, hour, minute, and second.

再说的明白点儿，DATE类型可以展示为"yyyy-mm-dd"，也可以展示为"yyyy-mm-dd hh24:mi:ss"，但是存储格式只有一种，即含时间的格式，"yyyy-mm-dd hh24:mi:ss"。

当指定存储“年月日”的日期时，他存储的是“年月日0点0分0秒”，在PLSQL Developer中，展示格式是"yyyy-mm-dd"(当然具体格式和Perferences的设置相关)，即未带时间的。当指定存储“年月日时分秒”的日期时，他存储的是“年月日时分秒”，在PLSQL Developer中，展示格式是"yyyy-mm-dd hh24:mi:ss"(当然具体格式和Perferences的设置相关)，即带时间的。

我们模拟下，

SQL> create table a (id number, cdate date);           
Table created.

SQL> insert into a values(1, to_date('2019-01-01','yyyy-mm-dd'));
1 row created.

SQL> insert into a values(1, to_date('2019-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'));
1 row created.

此时在PLSQL Developer中检索，能看到区别，

640?wx_fmt=png

使用to_char转换，可以看到，第一条记录，其实时间是00:00:00，

640?wx_fmt=png

下个问题，就是如何找到，这些所谓不带时间的记录，一开始是想通过extract函数，提取出“时分秒”，让其分别等于0，作为条件检索，

640?wx_fmt=png

但实际执行，提示错误ORA-00920，即无效的关系运算符，

SQL> select * from a where extract(hour from cdate)=00 and extract(minute from cdate)=00
  2  and extract(second from cdate);
and extract(second from cdate)
                             *
ERROR at line 2:
ORA-00920: invalid relational operator

看下extract的介绍，已经说了，如果需要HOUR、MINUTE或者SECOND，需要extract参数必须是TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND，不能是DATE，因为Oracle会将DATE看做是ANSI的DATE数据类型，他是不包含时间字段的，

If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.

既然我要找的，是时间为00:00:00的，通过to_char，指定"hh24:mi:ss"的格式，进行检索，是可以找到的，

SQL> select * from a WHERE to_char(cdate, 'hh24:mi:ss')='00:00:00';
        ID CDATE
---------- ----------
         1 2019-01-01

如果需要将其更新为23:59:59，就这么做，

SQL> UPDATE a SET cdate=cdate+(1-1/86400) WHERE to_char(cdate, 'hh24:mi:ss')='00:00:00';
1 row updated.

当然，如上只是测试，生产环境中，若数据量很大，需要在where中增加合适的条件，避免全表扫描，尤其是更新操作。

这个问题不复杂，但你要明白DATE数据类型的实际存储，进而找到如何检索记录的线路，另外，像extract这种的函数，Oracle中还有很多，一些不常用的，并不需要背下来，当需要的时候，你能找到语法，知道如何使用，就可以了。

我上面使用to_char的解决方案，可能只是其中一种方式，如果各位有更好的解决方案，欢迎提出来，共同学习。