Oracle中的Date、TimeStamp和Interval（上）

在Oracle中，Date和TimeStamp类型存储精度可变的固定日期时间。Intreval可以很容易地存储一个时间量，如“8个小时”或“30天”，将两个时间戳相减，就会得到一个时间间隔（Interval）,例如，将8小时加到一个TimeStamp上，即得到一个新的TimeStamp。

Oracle 5中已经支持Date类型，TimeStamp和Interval在Oracle 9i Release 1中才被引入。因此，Date最为常见，但你应该在新应用中使用TimeStamp类型，因为：

1、TimeStamp支持小数秒，而Date不支持；

2、TimeStamp支持时区，Date不支持。

（一）Date类型

Date类型是一个7字节的定宽日期/时间数据类型，它包含7个属性：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟、秒。Oracle使用一种内部格式来表示，它并不是保存20，12,10,31,12,01,00来表示2012年10月31日12：01：00，这个可以通过内置函数DUMP来查看：

Create table t (x date);
insert into t(x) values (to_date('2012-11-24 12:01:00','YYYY-MM-dd HH24:mi:ss'));
select x,dump(x,10) d from t;

查询结果：

世纪和年份字节，即（120，112）采用一种“加100”的表示法来存储。必须将其减去100才能得到正确的世纪和年份。这是为了支持BC和AD日期。
如果从世纪字节减100得到负数，表示BC日期，例如：

insert into t (x) values( to_date( '01-08-4712bc','dd-MM-yyyybc hh24:mi:ss' ) );

此时，会报一个错，“ORA-01856: 要求BC/B.C. 或AD/A.D.”。

修改为如下语句：

Insert into t (x) values(to_date('01-08-4712BC','dd-MM-YYYYBC','nls_date_language=American'));

insert into t (x) values( to_date( '01-08-4710BC 12:01:13','dd-MM-yyyyBC hh24:mi:ss'，'nls_date_language=American') );

查看结果：

别担心，读出的世纪和年份是“虚”的，其实记录是正确的，以下语句即可。

select x, to_char(x,'dd-mm-yyyybc','nls_date_language=American') AS TrueDate,dump(x,10) d from t;

 世纪字节是53，而53－100＝－47，这才是我们插入的真实世纪。由于这是一个负数，我们知道它是一个BC日期。这种存储格式还允许日期以一种二进制方式自然地排序。由于4712BC小于4710BC，我们可以测试一下排序：

select x, to_char(x,'dd-mm-yyyybc','nls_date_language=American') AS TrueDate,dump(x,10) d from t
order by TRUEDATE ASC;

 接下来月份和日字节，自然存储，不作任何修改。小时、分、秒采用“加1”表示法存储，也就是各部分都减1，才能得到真实的时间。因此，午夜0点对应的日期字段表示为1，1，1

这种7字节格式能自然排序，可以采用一种进二进制方式按从小到大或从大到小的顺序非常高效地进行排序。另外，这种结构允许很容易地进行截断，而无需把日期变成其他格式。即将尾部三个字节设置为1，1，1即可清除时间分量。

创建一个新表：

create table t ( what varchar2(10), x date );
insert into t (what, x) values
( 'downmoon',
  to_date( '25-11-2012 22:01:00',
           'dd-MM-yyyy hh24:mi:ss' ) );
insert into t (what, x)
select 'minute', trunc(x,'mi') from t
union all
select 'day', trunc(x,'dd') from t
union all
select 'month', trunc(x,'mm') from t
union all
select 'year', trunc(x,'y') from t
/
select what, x, dump(x,10) d from t;

结果：

 

要把这个日期截断，只取年份，数据库只要后5个字节置1，这是一个非常快速的操作。

而要向一个date增加时间或减去时间，请先遵循以下原则：

（1）使用NUMTODSINTERVAL增加时分秒；

（2）使用Date直接加数字增加天；

（3）使用ADD_MONTHS内置函数增加月和年；不建议使用NUMTOYMINTERVAL函数。因为前者会帮我们完成日期的舍入。

看示例：

alter session set nls_date_format='dd-MM-yyyy hh24:mi:ss';
select dt,add_months(dt,1) dt1,dt+NUMTOYMINTERVAL(1,'month') DT2
 from (select to_date('29-02-2012','dd-MM-yyyy')dt from dual);

注意：ADD_MONTHS是添加自然月，而NUMTOYMINTERVAL是加到下个月的同一天，不管这一天是否有效，如果3月31日使用NUMTOYMINTERVAL加一个月，系统会报错。

 因此，这句会正确执行：

select dt,add_months(dt,1) dt1
 from (select to_date('31-03-2012','dd-MM-yyyy')dt from dual);

而这句会报错：指定月份的日期无效。

 同理，向2012年2月29日增加1 年也会报错，因为不存在2013年2月29日。

 另外一个应用就是如何得到两个日期之差，方法如下：

 select numtoyminterval
       (trunc(months_between(dt2,dt1)),'month')
           years_months,
       numtodsinterval
           (dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),
            'day' )
           days_hours
  from (select to_date('2012-11-29 01:02:03','yyyy-MM-dd hh24:mi:ss') dt1,
               to_date('2014-03-15 11:22:33','yyyy-MM-dd hh24:mi:ss') dt2
          from dual );

这个结果清楚地表明了两个日期相差1年、3个月、15天、10小时、20分、30秒。

下面一节我们将了解了下TimeStamp