【转】Oracle日期类型操作几个问题

转自：

http://blog.itpub.net/17203031/viewspace-680840/

日期类型Date是Oracle的一个数据类型，将日期和时间以数字类型保存在数据库中的格式。

 

在官方文档（11gR1）中，对于Date类型数据描述如下：

 

“Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.It does not have fractional seconds or a time zone.”

 

这部分告诉我们Oracle的Date类型数据是表示自公元前4712年1月1日（也就是Julian Days，一种历法日期）到公园9999年1月1日。本质上应该是一种数字类型计数。Date类型的显示是受到Oracle系统参数nls_date_formate和nls_territory控制，根据不同的地区、语言，可以显示成不同的格式。存储容量上，Date类型占用7个字节大小。包括年、月、日、小时、分钟和秒。下面根据几个容易出现的问题进行分析：

 

1、  Date类型数据增减

这部分参考了eygle老师的一篇博文。

Date类型一个好处是可以直接进行日期的增减操作，通过加减符号就可以实现。如下例：

 

先设置日期显示格式

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

Session altered

 

处理加减

 

SQL> col now format a20;

SQL> col next_hour format a20;

SQL> col next_minute format a20;

SQL> col next_second format a20;

SQL> select sysdate as now, sysdate+1/24 as next_hour, sysdate+1/(24*60) as next_minute, sysdate+1/(24*60*60) as next_second from dual;

 

NOW          NEXT_HOUR        NEXT_MINUTE       NEXT_SECOND

-------------------- -------------------- -------------------- --------------------

2010-12-2 22:39:24   2010-12-2 23:39:24   2010-12-2 22:40:24   2010-12-2 22:39:25

 

上面可见，对日期类型数据进行加减，可以方便的进行日期推进和后退。标准是将一天作为整数1，其他如年、月、小时、分、秒都是依次放大或者缩小相应的倍数。如要想获得当前时间3分钟后的时间，就需要sysdate+3/(24*60)。这样的操作在实际中效率很高。

 

 

2、  日期类型参数的设置

日期的格式是显示日期的重要内容。但是有一点需要注意，日期的显示是与Oracle设置的日期格式、语言和地区相关的，但是本质上Date类型保存的数值是固定不变的。

 

参数nls_time_format可以直接对于日期格式的现实进行控制，如上述代码示例中显示的内容。此外，nls_territory和nls_date_language在一定程度上也会影响到日期格式的显示。通过视图v$nls_parameters，可以查看到预期有关的参数。

 

SQL>  select * from V$NLS_PARAMETERS;

 

PARAMETER                                       VALUE

----------------------------------------------------------------  ---------------------------------

NLS_LANGUAGE                                                     SIMPLIFIED CHINESE

NLS_TERRITORY                                                    CHINA

NLS_CALENDAR                                                     GREGORIAN

NLS_DATE_FORMAT                                                  DD-MON-RR

NLS_DATE_LANGUAGE                               SIMPLIFIED CHINESE

NLS_TIME_FORMAT                              HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM

……

 

19 rows selected

说明：上述代码省略了部分结果。

 

三个参数都可以影响到日期输出的格式。实际中，无论是直接书写服务器端代码，还是在应用中书写，最好都直接指定出转换日期格式。在进行数据库移植的时候，也要注意不同语言、日期格式的数据库进行转移时，要显示进行数据格式的设置。

三个参数中，比较常用的是nls_date_formate和nls_date_language。一般如果在session进行格式规范，可以如下：

 

SQL> alter session set NLS_DATE_LANGUAGE = 'SIMPLIFIED CHINESE';

Session altered

 

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24-MI-SS';

Session altered

 

 

3、“J”格式符

在对Date类型转换为字符类型的时候，有一些代码中使用到了“J”。如下：

 

SQL> select to_char(sysdate,'J') from dual;

 

TO_CHAR(SYSDATE,'J')

--------------------

2455533

 

J实际表示的Julian Days，返回的就是该日期与Julian Days之间的天的数量差。

 

 

4、  进行日期范围查询

Date类型除了包括年月日信息之外，还包括时间精确的信息。对于一些应用而言，这部分时间精确信息反而成为了一种负担。有时候，也因此避免选择Date作为日期信息类型，而去选择Number类型作为替代。

 

当进行日期查询时，就会存在一些问题。如下：

 

构造数据表tt。

 

SQL> create table tt as select object_id, created from dba_objects where 1=0;

 

Table created

SQL> insert into tt select rownum, sysdate from dba_objects where rownum<4;

 

3 rows inserted

SQL> commit;

 

Commit complete

 

搜索与今天相同日期的方法。

SQL> select * from tt where created = to_date (to_char (sysdate, 'yyyy-mm –dd ' ),'yyyy-mm-dd');

 

 OBJECT_ID CREATED

---------- -----------

 

 

查询不到数据的原因很简单。因为插入数据的时候，sysdate是携带着时间信息的，如果简单按照天来进行查询，是没有结果。解决的方法有下面几个，各有利弊：

 

方法一：在查找字段下手处理，也是最简单的方法。

SQL> select * from tt where to_char(created,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');

 

 OBJECT_ID CREATED

---------- -----------

         1 2010-12-2 2

         2 2010-12-2 2

         3 2010-12-2 2

 

首先，这样做可以获取到正确的结果。而且语句的复杂程度也可以接受。缺点是在字段加入了函数操作，一般在created上的索引是无法其效果的。当然，这个问题也可以通过函数索引来解决，在created字段上加一个to_char()操作的索引。

SQL> create index ind_tt on tt(to_char(created,'yyyy-mm-dd'));

 

Index created

 

方法二：使用范围查询代替等于查询。

不在搜索字段上进行处理，转化为搜索全天时间范围。

 

SQL> select * from tt where created>=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and created < to_date (to_char(sysdate+1,' yyyy-mm-dd'),'yyyy-mm-dd');

 

 OBJECT_ID CREATED

---------- -----------

         1 2010-12-2 2

         2 2010-12-2 2

         3 2010-12-2 2

 

这种方法可以便于利用索引，也容易在日期范围查询功能上复用，缺点是复杂度略高。

 

 

结论：日期Date是Oracle数据库中一种常见的数据类型。无论是存储效率还是操作上，都已经是比较成熟的技术类型。本文只是针对几个常见问题进行讨论说明，其他如各种日期函数使用、间隔Interval类型使用等问题，留待日后继续讨论。