再上一篇:12.4数值类型
上一篇:12.5LONG类型
主页
下一篇:12.7 LOB类型
再下一篇:12.8 ROWID/UROWID类型
文章列表

12.6DATE、TIMESTAMP和 INTERVAL类型

Oracle 9i 10g编程艺术:深入数据库体系结构

Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。DATE和TIMESTAMP类型存储精度 可变的固定日期/时间。INTERVAL类型可以很容易地存储一个时间量,如“8个小时”或“30天”。将两个 日期相减,就会得到一个时间间隔(INTERVAL);例如,将 8小时间隔加到一个TIMESTAMP上,会得到8小 时以后的一个新的TIMESTAMP。
Oracle 的很多版本中都支持DATE数据类型,这甚至可以追溯到我最早使用Oracle的那个年代,也 就是说,至少在Oracle 5中(可能还更早)就已经支持DATE数据类型。TIMESTAMP和INTERVAL类型相对 来讲算是后来者,因为它们在Oracle9i Release 1中才引入。由于这个简单的原因,你会发现DATE数据 类型是存储日期/时间信息最为常用的类型。但是许多新应用都在使用 TIMESTAMP类型,这有两个原因:一 方面它支持小数秒,而DATE类型不支持;另一方面TIMESTAMP类型支持时区,这也是DATE类型力所不能 及的。
我们先来讨论DATE/TIMESTAMP格式及其使用,然后介绍以上各个类型。

12.6.1格式

这里我不打算全面介绍DATE、TIMESTAMP和INTERVAL格式的方方面面。这在Oracle SQL Reference 手册中有很好的说明,任何人都能免费得到这本手册。你可以使用大量不同的格式,很好地了解这些格式 至关重要。强烈推荐你先好好研究一下各种格式。
在此我想讨论这些格式会做什么,因为关于这个主题存在许多误解。这些格式用于两个目的: 以某种格式对数据库中的数据进行格式化,以满足你的要求。 告诉数据库如何将一个输入串转换为DATE、TIMESTAMP或INTERVAL。
仅此而已。多年来我观察到的一个常见的误解是,使用的格式会以某种方式影响磁盘上存储的数据, 并且会影响数据如何具体地存储。格式对数据如何存储根本没有任何影响。格式只是用于将存储DATE所用 的二进制格式转换为一个串,或者将一个串转换为用于存储DATE的二进制格式。对于TIMESTAMP和INTERVAL 也是如此。
关于格式,我的建议就是:应该使用格式。将一个表示 DATE、TIMESTAMP或INTERVAL的 串发送到数 据库时就可以使用格式。不要依赖于默认日期格式,默认格式会(而且很可能)在将来每个时刻被另外每 个人所修改。如果你依赖于一个默认日期格式, 而这个默认格式有了变化,你的应用可能就会受到影响。

如果无法转换日期,应用可能会向最终用户返回一个错误;或者更糟糕的是,它可能会悄悄地插入错误的 数 据。考虑以下INSERT语句,它依赖于一个默认的日期掩码:
Insert into t ( date_column ) values ( '01/02/03' );
假设应用依赖于必须有默认日期掩码DD/MM/YY。这就表示2003年2月1 日(假设代码在2000年之

后执行,不过稍后还会再来讨论有关的问题)。现在,假设有人认为正确而且适当的日期格式应该是 MM/DD/YY。突然之间,原来的日期就会变成2003年1月2日。或者有人认为YY/MM/DD才对,现在的日期 就会变成2001年2月3 日。简单地说,如果日期串没有带相应的日期格式,就会有多种解释方法。这个 INSERT语句最好写作:
Insert into t ( date_column ) values ( to_date( '01/02/03', 'DD/MM/YY' ) );

按我的观点,更好的做法是:
Insert into t ( date_column ) values ( to_date( '01/02/2003', 'DD/MM/YYYY' ) );
也就是说,它必须使用一个4 字符的年份。并不算太久以前,整个行业都领教了捡芝麻而丢西瓜的切
肤之痛,我们原本想在存储年份时“节省”2 个字节,可是这带来了重重问题,为了解决这些问题,相应 地修补软件,想想看我们花了多少时间和精力。可是,随着时间的推移,我们好像又好了伤疤忘了疼。现 如今,2005年都过去了,如果还不使用4字符的年份实在说不过去!
从数据库取出的数据也同样存在上述问题。如果你执行 SELECT DATE_COLUMN FROM T,并在应用中把 这一列获取到一个串中,就应该对其应用一个显示的日期格式。不论你的应用期望何种格式,都应该在这 里显式指定。否则,如果将来某个时刻有人修改了默认日期格式,你的应用就可能会崩溃,或者有异样的 表现。
接下来,我们来更详细地介绍各种日期数据类型。

12.6.2 DATE类型


DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性,包括:世纪、世纪中哪一 年、月份、月中的哪一天、小时、分钟和秒。Oracle使用一种内部格式来表示这个信息,所以它并不是存 储20,05,06,25,12.,01,00来表示2005年6月25日12.:01:00。通过使用内置DUMP函数,可以看 到Oracle实际上会存储以下内容:
ops$tkyte@ORA10G> create table t ( x date );
Table created.
ops$tkyte@ORA10G> insert into t (x) values
2 ( to_date( '25-jun-2005 12.:01:00',
3 'dd-mon-yyyy hh24:mi:ss' ) );
12.row created.

ops$tkyte@ORA10G> select x, dump(x,12.) d from t; X D
--------- -----------------------------------
25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1
世纪和年份字节(DUMP输出中的120,105)采用一种“加100”(excess-100)表示法来存储。必须

将其减去100来确定正确的世纪和年份。之所以采用加100表示法,这是为了支持BC和AD日期。如果从 世纪字节减去100得到一个负数,则是一个BC日期,例如:
ops$tkyte@ORA10G> insert into t (x) values
2 ( to_date( '01-jan-4712bc',
3 'dd-mon-yyyybc hh24:mi:ss' ) );
12.row created.
ops$tkyte@ORA10G> select x, dump(x,12.) d from t; X D
--------- -----------------------------------
25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1
01-JAN-12. Typ=12. Len=7: 53,88,12.12.12.12.1
因此,插入01-JAN-4712BC时,世纪字节是53,而53-100=-47,这才是我们插入的真实世纪。由于

这是一个负数,所以我们知道它是一个BC日期。这种存储格式还允许日期已一种二进制方式自然地排序。 由于4712 BC小于4710 BC,我们希望能有一种支持这种顺序的二进制表示。通过转储这两个日期,可以 看到01-JAN-4710BC比4712 BC中的同一天“更大”,所以它们确实能正确地排序,并很好地进行比较:
ops$tkyte@ORA10G> insert into t (x) values
2 ( to_date( '01-jan-4710bc',
3 'dd-mon-yyyybc hh24:mi:ss' ) );
12.row created.
ops$tkyte@ORA10G> select x, dump(x,12.) d from t;

X D
--------- -----------------------------------
25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1
01-JAN-12. Typ=12. Len=7: 53,88,12.12.12.12.1
01-JAN-12. Typ=12. Len=7: 53,90,12.12.12.12.1
接下来两个字段是月份和日字节,它们会自然地存储,不做如何修改。因此,6月25日的月份字节
就是6,日字节是25.小时、分钟和秒字段采用“加1”(excess-1)表示法存储,这说明必须将各个部分 减1,才能得到实际的时间。因此,午夜0点在日期字段中就表示为12.12.1。

这种7字节格式能自然地排序。你已经看到了,这一个7字节字段,可以采用一种二进制方式按从小 到大(或从大到小)的顺序非常高效地进行排序。另外,这种结构允许很容易地进行截断,而无需把日期 转换为另外某种格式。例如,要截断刚才存储的日期(25-JUN-2005 12.:01:00)来得到日信息(去掉小时 、 分钟和秒字段),这相当简单。只需将尾部的 3个字节设置为12.12.1,就能很好地清除时间分量。考虑一 个全新的表T,并执行以下插入:
ops$tkyte@ORA10G> create table t ( what varchar2(12.), x date );
Table created.
ops$tkyte@ORA10G> insert into t (what, x) values
2 ( 'orig',
3 to_date( '25-jun-2005 12.:01:00',
4 'dd-mon-yyyy hh24:mi:ss' ) );
12.row created.
ops$tkyte@ORA10G> insert into t (what, x)
2 select 'minute', trunc(x,'mi') from t
3 union all
4 select 'day', trunc(x,'dd') from t
5 union all
6 select 'month', trunc(x,'mm') from t

7 union all
8 select 'year', trunc(x,'y') from t
9 /
4 rows created.
ops$tkyte@ORA10G> select what, x, dump(x,12.) d from t; WHAT X D
-------- --------- ----------------------------------- orig 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1
minute 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.,2,1
day 25-JUN-05 Typ=12. Len=7: 120,105,6,25,12.12.1 month 01-JUN-05 Typ=12. Len=7: 120,105,6,12.12.12.1
year 01-JAN-05 Typ=12. Len=7: 120,105,12.12.12.12.1
要把这个日期截断,只取到年份,数据库所要做的只是在后5个字节上置1,这是一个非常快速的操

作。现在我们就有一个可排序、可比较的DATE字段,它能截断到年份级,而且我们可以尽可能高效地做到 这一点。不过,许多人并不是使用TRUNC,而是在TO_CHAR函数中使用一个日期格式。例如,他们会这样 用:
Where to_char(date_column,'yyyy') = '2005'

而不是
Where trunc(date_column,'y') = to_date('01-jan-2005','dd-mon-yyyy')
后者不仅有更出色的表现,而且占有的资源更少。如果建立 ALL_OBJECTS的一个副本,另存储其中的

CREATED列:
ops$tkyte@ORA10G> create table t
2 as
3 select created from all_objects; Table created.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

然后,启用SQL_TRACE,我们反复使用上述两种技术查询这个表,可以看到以下结果:
select count(*)
from
t where to_char(created,'yyyy') = '2005'

call count cpu elapsed disk query cur rent rows

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

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

Parse 4 0.01 0.05 0 0

0 0

Execute 4 0.00 0.00 0 0

0 0

Fetch 8 0.41 0.59 0 372

0 4

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

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

total 12. 0.42 0.64 0 372

0 4

select count(*)
from
t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy')

call count cpu elapsed disk query cur rent rows

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

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

Parse 4 0.00 0.00 0 0

0 0

Execute 4 0.00 0.00 0 0

0 0

Fetch 8 0.04 0.12. 0 372

0 4

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

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

total 12. 0.04 0.12. 0 372

0 4


可以看到存在明显的差异。与使用TRUNC相比,使用TO_CHAR所用的CPU时间与前者相差一个数量级
(即相差12.倍)。这是因为 TO_CHAR必须把日期转换为一个串,这要使用一个更大的代码路径,并利用当 前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面,TRUNC只需把后5 个字节设 置为1.然后将两个7 字节的二进制数进行比较,就大功告成了。因此,如果只是要截断一个DATE列,你 将应该避免使用TO_CHAR。
另外,甚至要尽可能完全避免对DATE列应用函数。把前面的例子再进一步,可以看到其目标是获取

2005年的所有数据。那好,如果CREATED上有一个索引,而且表中只有很少一部分CREATED值是2005年 的时间,会怎么样呢?我们可能希望能够使用这个索引,为此要使用一个简单的谓词来避免在数据库列上 应用函数:
select count(*) from t
where created >= to_date('01-jan-2005','dd-mon-yyyy')
and created < to_date('01-jan-2006','dd-mon-yyyy');
这样有两个好处:
这样一来就可以考虑使用CREATED上的索引了。 根本无需调用TRUNC函数,这就完全消除了相应的开销。
这里使用的是区间比较而不是TRUNC或TO_CHAR,这种技术同样适用于稍后讨论的TIMESTAMP类型。 如果能在查询中避免对一个数据库列应用函数,就应该全力这样做。一般来讲,避免使用函数会有更好的 性能,而且允许优化器在更多的访问路径中做出选择。

1. 向 DATE 增加或减去时间

经常有人问我这样一个问题:“怎么向一个DATE类型增加时间,或者从DATE类型减去时间?”例如, 如何向一个DATE增加1天,或8个小时,或者一年,或者一个月,等等?对此,常用的技术有3种:
向DATE增加一个NUMBER。把DATE加1是增加1天的一种方法。因此,向DATE增加12.24 就是增加1 个小时,依此类推。
可以使用稍后将介绍的INTERVAL类型来增加时间单位。INTERVAL类型支持两种粒度:年和 月,或日/小时/分钟/秒。也就是说,可以是几年和几个月的一个时间间隔,也可以是几天、几
小时、几分钟和几秒的一个时间间隔。
使用内置的 ADD_MONTHS 函数增加月。由于增加一个月往往不像增加 28~31 天那么简单, 为了便于增加月,专门实现了这样一个函数。
表12.-3展示了向一个日期增加N个时间单位可用的技术(当然,也可以利用这些技术从一个日期减 去N个时间单位)。
表12.-3 向日期增加时间
时间单位 操作 描述

DATE + n/24/60/60 一天有 86,400 秒。由于加 1 就是增加 1 天,所以加 12.86400 就是向一个日期增

N秒 DATE + n/86400 加 1秒。我更喜欢用 n/24/60/60技术而不是 12.86400技术。它 们是等价的。

DATE+NUMTODSINTERVAL 更可读的一种方法是使用 NUMTODSINTERVAL(日/秒数间隔) (n,'second') 函数来增加 N 秒

DATE + n/24/60 一天有 12.440 分钟,因此加 12.1440就是向一个 DATE 增加1 分钟。更可读的

N分钟 DATE + n/1440 一种方法是使用 NUMTODSINTERVAL函数 DATE+NUMTODSINTERVAL

(n,'minute') DATE + n/24

N小时 DATE+NUMTODSINTERVAL 一天有 24个小时,因此增加 12.24就是向一个 DATE增加 1 小时。更可读的 (n,'hour') 一种方法是使用 NUMTODSINTERVAL函数

N天 DATE + n 向 DATE直接加 N,就是增加或减去 N 天

N周 DATE + 7*n 一周有 7 天,所以只需将周数乘以 7 就是要增加或减去的天 数

N月 ADD_MONTHS(DATE,n) 可以使用 ADD_MONTHS内置函数或者向 DATE 增加一个 N个月的间隔。 DATE+NUMTOYMINTERVAL 请参考稍后有关 DATE 使用月间隔做出的重要警告

(n,'month')

N年 ADD_MONTHS(DATE,12.*n) 可以使用 ADD_MONTHS内置函数,增加或减去 N年时将参数指定为 12.*n。

DATE+NUMTOYMINTERVAL 利用年间隔可以达到类似的目标,不过请参考稍后有关日期使用年间隔做出

(n,'year') 重要警告

总的来讲,使用Oracle DATE类型时,我有以下建议:
使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。 加一个简单的数来增加天。
使用ADD_MONTHS内置函数来增加月和年。
我建议不要使用NUMTOYMINTERVAL函数。其原因与这个函数如何处理月末日期有关。

ADD_MONTHS函数专门处理月末日期。它实际上会为我们完成日期的“舍入”;例如,如果向一个有31 天的月增加1个月,而且下一个月不到31 天,ADD_MONTHS就会返回下一个月的最后一天。另外,向一个 月的最后一天增加1 个月会得到下一个月的最后一天。向有30天(或不到30天)的一个月增加1个月时, 可以看到:
ops$tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
---------------------------- ------------------------------
29-feb-2000 00:00:00 31-mar-2000 00:00:00
ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)

-------------------- --------------------
28-feb-2001 00:00:00 31-mar-2001 00:00:00
ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
30-jan-2001 00:00:00 28-feb-2001 00:00:00
ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
30-jan-2000 00:00:00 29-feb-2000 00:00:00
看到了吗?向2000年2月29日增加1个月,得到的是2000年3月 31日。2月29 日是该月的最后
一天,所以ADD_MONTHS返回了下一个月的最后一天。另外,注意向2000年和2001年的1月30日增加1 个月时,会分别得到2000年和2001年2月的最后一天(分别是2 月29日和2月28日)。

如果与增加一个间隔的做法相比较,会看到完全不同的结果:
ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
3 /
DT DT+NUMTOYMINTERVAL(1
-------------------- --------------------

29-feb-2000 00:00:00 29-mar-2000 00:00:00
ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
3 /
DT DT+NUMTOYMINTERVAL(1
-------------------- --------------------
28-feb-2001 00:00:00 28-mar-2001 00:00:00
注意,得到的日期并不是下一个月的最后一天,而只是下一个月的同一天。有人认为这种行为是可以

接受的,但是请考虑一下,如果下一个月没有这么多天会怎么样:
ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
3 /
select dt, dt+numtoyminterval(1,'month')
*
ERROR at line 1:
ORA-01839: date not valid for month specified ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
3 /
select dt, dt+numtoyminterval(1,'month')
*
ERROR at line 1:
ORA-01839: date not valid for month specified
根据我的经验,这是由于这个原因,所以一般来讲不可能在日期算术运算中使用月间隔。对于年间隔
也存在一个类似的问题:如果向2000年2月29日增加1年,也会得到一个运行时错误,因为没有2001年
2月29日。

2. 得到两个日期之差


还有一个常被问到的问题:“我怎么得到两个日期之差?”这个问题看上去似乎很简单:只需要相减 就行了。这会返回表示两个日期相隔天数的一个数。另外,还可以使用内置函数 MONTHS_BETWEEN,它会返 回表示两个日期相隔月数的一个数(包括月小数)。最后,利用INTERVAL类型,你还能用另一个方法来查 看两个日期之间的逝去时间。以下SQL查询分别展示了将两个日期相减的结果(显示两个日期之间的天数), 使用MONTHS_BETWEEN函数的结果,然后是使用INTERVAL类型的两个函数的结果:
ops$tkyte@ORA10G> select dt2-dt1 ,
2 months_between(dt2,dt1) months_btwn,
3 numtodsinterval(dt2-dt1,'day') days,
4 numtoyminterval(months_between(dt2,dt1),'month') months
5 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
6 to_date('12.-mar-2001 12.:22:33','dd-mon-yyyy hh24:mi:ss') dt2
7 from dual )
DT2-DT1 MONTHS_BTWN DAYS MONTHS
---------- ----------- -----------------------
------- -------------
380.430903 12..5622872 +000000380 12.:20:
30.000000000 +000000001-00
这些都是“正确”的值,但是对我们来说都没有大用。大多数应用都更愿意显示日期之间相隔的年数 、
月数、天数、小时数、分钟数和秒数。通过使用前述函数的一个组合,就可以实现这个目标。我们将选出 两个间隔:一个是年和月间隔,另一个是日/小时/分钟/秒间隔。我们使用MONTHS_BETWEEN内置函数来确 定两个日期之间相隔的月数(包括小数),然后使用NUMTOYMINTERVAL内置函数将这个数转换为年数和月数 。 另外,使用TRUNC得到两个日期相隔月数中的整数部分,再使用ADD_MONTHS内置函数将dt1增加12.个月

(这会得到‘28-feb-2001 01:02:03),再从两个日期中的较大者(dt2)减去这个计算得到的日期,从而 得到两个日期之间的天数和小时数:
ops$tkyte@ORA10G> select numtoyminterval
2 (months_between(dt2,dt1),'month')
3 years_months,

4 numtodsinterval
5 (dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),
6 'day' )
7 days_hours
8 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
9 to_date('12.-mar-2001 12.:22:33','dd-mon-yyyy hh24:mi:ss') dt2
12. from dual )
12. /
YEARS_MONTHS DAYS_HOURS
--------------- ------------------------------
+000000001-00 +000000015 12.:20:30.000000000
现在就很清楚了,这两个日期之间相隔1年、12.天、12.小时、20分钟和30秒。

12.6.3 TIMESTAMP类型

TIMESTAMP类型与 DATE非常类似,只不过另外还支持小数秒和时区。以下3 小节将介绍 TIMESTAMP 类型:其中一节讨论只支持小数秒而没有时区支持的情况,另外两小节讨论存储有时区支持的TIMESTAMP 的两种方法。

1. TIMESTAMP


基本TIMESTAMP数据类型的语法很简单:
TIMESTAMP(n)
这里N是可选的,用于指定TIMESTAMP中秒分量的小数位数,可以取值为0~9.如果指定0,TIMESTAMP

在功能上则与DATE等价,它们实际上会以同样的方式存储相同的值:
ops$tkyte@ORA10G> create table t
2 ( dt date,
3 ts timestamp(0)
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t values ( sysdate, systimestamp );
12.row created.
ops$tkyte@ORA10G> select dump(dt,12.) dump, dump(ts,12.) dump
2 from t;
DUMP DUMP
------------------------------------ --------------------------
----------
Typ=12. Len=7: 120,105,6,28,12.,35,41 Typ=180 Len=7: 120,105,6,28,12.,35,41

这两个数据类型是不同的(由TYP=字段可知),但是它们采用了相同的方式存储数据。如果指定要保

留几位秒小数,TIMESTAMP数据类型与DATE类型的长度将会不同,例如:
ops$tkyte@ORA10G> create table t
2 ( dt date,
3 ts timestamp(9)
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t values ( sysdate, systimestamp );
12.row created.
ops$tkyte@ORA10G> select dump(dt,12.) dump, dump(ts,12.) dump
2 from t;
DUMP DUMP

------------------------------------- ---------------------------
----------
Typ=12. Len=7: 120,105,6,28,12.,46,21 Typ=180 Len=12.: 120,105,6,28,12.,46,21
,44,101,192,2
08
现在TIMESTAMP占用12.字节的存储空间,最后额外的4个字节包含着小数秒,通过查看所存储的时

间就能看出:
ops$tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA10G> select * from t;
DT TS
-------------------- --------------------------------
28-jun-2005 12.:45:20 28-JUN-05 12..45.20.744866000 AM
ops$tkyte@ORA10G> select dump(ts,12.) dump from t; DUMP
-------------------------------------------------- Typ=180 Len=12.: 78,69,6,12.,b,2e,12.,2c,65,c0,d0
ops$tkyte@ORA10G> select to_number('2c65c0d0','xxxxxxxx') from dual; TO_NUMBER('2C65C0D0','XXXXXXXX')
--------------------------------
744866000
可以看到,存储的小数秒都在最后4 个字节中。这一次我们使用了DUMP函数以HEX(十六进制)来
查看数据,所以能很容易地将这4 个字节转换为十进制表示。

2. 向 TIMESTAMP 增加或减去时间


DATE 执行日期算术运算所用的技术同样适用于 TIMESTAMP,但是在使用上述技术的很多情况下, TIMESTAMP会转换为一个DATE,例如:
ops$tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA10G> select systimestamp ts, systimestamp+12.dt
2 from dual;
TS DT
------------------------------------ --------------------
28-JUN-05 12..04.49.833097 AM -04:00 29-jun-2005 12.:04:49
注意,这里加1 实际上将SYSTIMESTAMP推进了1天,但是小数秒没有了,另外时区信息也没有了。

这里使用INTERVAL更有意义:
ops$tkyte@ORA10G> select systimestamp ts, systimestamp +numtodsinterval(1,'day') dt
2 from dual;
TS DT
------------------------------------ -------------------------
---------------
28-JUN-05 12..08.03.958866 AM -04:00 29-JUN-05 12..08.03.958866000 AM -04:00
使用返回一个INTERVAL类型的函数能保持TIMESTAMP的真实度。使用TIMESTAMP时要特别当心,以
避免这种隐式转换。
但是还要记住,向TIMESTAMP增加月间隔或年间隔时存在相关的警告。如果所得到的“日期”不是一 个合法日期,这个操作就会失败(如果通过INTERVAL来增加月,向一月份的最后一天增加1个月总会失败 , 因为这会得到“2月31日”,而2月根本没有31天)。

3. 得到两个TIMESTAMP 之差


这正是 DATE 和 TIMESTAMP类型存在显著差异的地方。尽管将 DATE相减的结果是一个 NUMBER,但 TIMESTAMP相减的结果却是一个INTERVAL:
ops$tkyte@ORA10G> select dt2-dt1
2 from (select to_timestamp('29-feb-2000 01:02:03.122000',
3 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('12.-mar-2001 12.:22:33.000000',

5 'dd-mon-yyyy hh24:mi:ss.ff') dt2
6 from dual )
7 / DT2-DT1
---------------------------------------------------------------------------
+000000380 12.:20:29.878000000
两个TIMESTAMP值之差是一个INTERVAL,而且这里显示了二者之间相隔的天数已经小时/分钟/秒数。

如果想得到二者之间相差的年数和月数,可以使用以下查询(这个查询类似于先前用于日期的查询):
ops$tkyte@ORA10G> select numtoyminterval
2 (months_between(dt2,dt1),'month')
3 years_months,
4 dt2-add_months(dt1,trunc(months_between(dt2,dt1)))
5 days_hours
6 from (select to_timestamp('29-feb-2000 01:02:03.122000',
7 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
8 to_timestamp('12.-mar-2001 12.:22:33.000000',
9 'dd-mon-yyyy hh24:mi:ss.ff') dt2
12. from dual )
12. /
YEARS_MONTHS DAYS_HOURS
------------- -----------------------------
+000000001-00 +000000015 12.:20:30.000000000
需要说明,在这种情况下,由于使用了ADD_MONTHS,DT1会隐式转换为一个DATE类型,这样就丢失

了小数秒。为了保住小数秒,我们必须编写更多的代码。也许可以使用NUMTOYMINTERVAL来增加月,这样 就能保留TIMESTAMP;不过,这样一来,我们将遭遇运行时错误:
ops$tkyte@ORA10G> select numtoyminterval

2 (months_between(dt2,dt1),'month')
3 years_months,
4 dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),'month' ))
5 days_hours
6 from (select to_timestamp('29-feb-2000 01:02:03.122000',
7 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
8 to_timestamp('12.-mar-2001 12.:22:33.000000',
9 'dd-mon-yyyy hh24:mi:ss.ff') dt2
12. from dual )
12. /
dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),'month' ))
*
ERROR at line 4:
ORA-01839: date not valid for month specified
我个人认为这是不能接受的。不过,问题在于,在你显示有年和月的信息时,TIMESTAMP的真实性已
经被破坏了。一年有多长并不固定(可能是365天,也可能是366天),同样,月的长度也不固定。如果你 在显示有年和月的信息,毫秒级的损失则无关大碍;显示这种信息时细到秒级就完全足够了。

4. TIMESTAMP WITH TIME ZONE 类型


TIMESTAMP WITH TIME ZONE类型继承了TIMESTAMP类型的所有特点,并增加了时区支持。TIMESTAMP WITH TIME ZONE类型占 12.字节的存储空间,在此有额外的 2 个字节用于保留时区信息。它在结构上与 TIMESTAMP的差别只是增加了这2个字节:
ops$tkyte@ORA10G> create table t
2 (
3 ts timestamp,
4 ts_tz timestamp with time zone
5 )
6 /

Table created.
ops$tkyte@ORA10G> insert into t ( ts, ts_tz )
2 values ( systimestamp, systimestamp );
12.row created.
ops$tkyte@ORA10G> select * from t;
TS TS_TZ
---------------------------- -----------------------------------
28-JUN-05 01.45.08.087627 PM 28-JUN-05 01.45.08.087627 PM -04:00
ops$tkyte@ORA10G> select dump(ts), dump(ts_tz) from t; DUMP(TS)
------------------------------------------------------------------------------- DUMP(TS_TZ)
------------------------------------------------------------------------------- Typ=180 Len=12.: 120,105,6,28,12.,46,9,5,57,20,248
Typ=181 Len=12.: 120,105,6,28,12.,46,9,5,57,20,248,12.,60
可以看到,获取数据时,默认的 TIMESTAMP WITH TIME ZONE格式包括有时区信息(执行这个操作时,
我所在的时区是East Coast US,当时正是白天)。
存储数据时,TIMESTAMP WITH TIME ZONE会在数据中存储指定的时区。时区成为数据本身的一部分。 注意TIMESTAMP WITH TIME ZONE字段如何存储小时、分钟和秒(…12.,46,9…),这里采用了加 1表示法, 因此…12.,46,9…就表示12.:45:08,而…12.,46,9…字段只存储了…12.,46,9…,这表示12.:45:09,也 就是我们插入到串中的那个时间。TIMESTAMP WITH TIME ZONE为它增加了4个小时,从而存储为GWT(也 称为UTC)时间。获取时,会使用尾部的2个字节适当地调整TIMESTAMP值。
我 并不打算在这里全面介绍时区的所有细节;这个主题在其他资料中已经做了很好的说明。我只是 要指出,与此前相比,时区支持对于今天的应用更显重要。十年前, 应用不像现在这么具有全球性。在因 特网普及之前,应用更多地都是分布和分散的,隐含地时区都基于服务器所在的位置。如今,由于大型的
集中式系统可能由世界 各地的人使用,所以记录和使用时区非常重要。 将时区支持内建到数据类型之前,必须把DATE存储在一个列中,而把时区信息存储在另外一列中,
然后要由应用负责使用函数将DATE从一个时区转换到另一个时区。现在则不然,这个任务已经交给了数据

库,数据库能存储多个时区的数据:
ops$tkyte@ORA10G> create table t
2 ( ts1 timestamp with time zone,
3 ts2 timestamp with time zone
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t (ts1, ts2)
2 values ( timestamp'2005-06-05 12.:02:32.212 US/Eastern',
3 timestamp'2005-06-05 12.:02:32.212 US/Pacific' );
12.row created.

并对这些数据执行正确的TIMESTAMP运算:
ops$tkyte@ORA10G> select ts1-ts2 from t;
TS1-TS2
---------------------------------------------------------------------------
-000000000 03:00:00.000000
因为这两个时区之间有3个小时的时差,尽管它们显示的是“同样的时间”——12.:02:32:212,但
是从报告的时间间隔来看确实存在3小时的时差。在TIMESTAMP WITH TIME ZONE类型上执行TIMESTAMP运 算时,Oracle会自动地把两个类型首先转换为UTC时间,然后执行运算。

5. TIMESTAMP WITH LOCAL TIME ZONE 类型


这种类型与TIMESTAMP类型的工作是类似的。这是一个7字节或12.字节的字段(取决于TIMESTAMP 的精度),但是会进行规范化,在其中存入数据库的时区。要了解这一点,我们将再次使用DUMP命令。首 先,创建一个包括3列的表,这3列分别是一个DATE列、一个 TIMESTAMP WITH TIME ZONE列和一个TIMESTAMP WITH LOCAL TIME ZONE列,然后向这3列插入相同的值:
ops$tkyte@ORA10G> create table t

2 ( dt date,
3 ts1 timestamp with time zone,
4 ts2 timestamp with local time zone
5 )
6 /
Table created.
ops$tkyte@ORA10G> insert into t (dt, ts1, ts2)
2 values ( timestamp'2005-06-05 12.:02:32.212 US/Pacific',
3 timestamp'2005-06-05 12.:02:32.212 US/Pacific',
4 timestamp'2005-06-05 12.:02:32.212 US/Pacific' );
12.row created.
ops$tkyte@ORA10G> select dbtimezone from dual; DBTIMEZONE
----------
US/Eastern
现在,将这些值转储如下:
ops$tkyte@ORA10G> select dump(dt), dump(ts1), dump(ts2) from t;
DUMP(DT)
------------------------------------ DUMP(TS1)

------------------------------------ DUMP(TS2)

------------------------------------ Typ=12. Len=7: 120,105,6,5,12.,3,33
Typ=181 Len=12.: 120,105,6,6,12.3,33,12.,162,221,0,137,156
Typ=231 Len=12.: 120,105,6,5,21,3,33,12.,162,221,0
可以看到,在这个例子中,会存储3种完全不同的日期/时间表示:
DT:这一列存储了日期/时间5-JUN-2005 12.:02:32。时区和小数秒没有了,因为我们使用 的是DATE类型。这里根本不会执行时区转换。我们会原样存储插入的那个日期/时间,但是会 丢掉时区。
TS1:这一列保留了TIME ZONE信息,并规范化为该TIME ZONE相应的UTC时间。所插入的 TIMESTAMP值处于 US/Pacific 时区,在写这本书时这个时间与 UTC 时间相差 7 个小时。因此, 存储的日期/时间是6-JUN-2005 00:02:32.212。它把输入的时间推进了7 个小时,使之成为UTC 时间,并把时区US/Pacific保存为最后2个字节,这样以后就能适当地解释这个数据。
TS2:这里认为这个列的时区就是数据库时区,即US/Eastern。现在,12.:02:32 US/Pacific is 20:02:32 US/Eastern,所以存储为以下字节(...21,3,33...),这里采用了加 1 表示法: 取得实际时间时要记住减1。

由于TS1列在最后2字节保留了原来的时区,获取时我们会看到以下结果:
ops$tkyte@ORA10G> select ts1, ts2 from t;
TS1
---------------------------------------- TS2
----------------------------------------
05-JUN-05 05.02.32.212000 PM US/PACIFIC
05-JUN-05 08.02.32.212000 PM
数据库应该能显示这个信息,但是有 LOCAL TIME ZONE(数据库时区)的TS2列只显示了数据库时区
的时间,并认为这就是这一列的时区(实际上,这个数据库中有LOCAL TIME ZONE的所有列的时区都是数 据库时区)。我的数据库处于 US/Eastern时区,所以插入的12.:02:32 US/Pacific现在显示为8:00pm East Coast时间。
如果你不需要记住源时区,只需要这样一种数据类型,要求能对日期/时间类型提供一致的全球性处 理,那么 TIMESTAMP WITH LOCAL TIME ZONE对大多数应用来说已经能提供足够的支持。另外,TIMESTAMP(0) WITH LOCAL TIME ZONE是与DATE类型等价但提供了时区支持的一种类型;它占用7字节存储空间,允许 存储按UTC形式“规范化”的日期。
关于TIMESTAMP WITH LOCAL TIME ZONE类型有一个警告,一旦你创建有这个列的表,会发现你的数

ops$tkyte@ORA10G> alter database set time_zone = 'PST';
alter database set time_zone = 'PST'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns ops$tkyte@ORA10G> !oerr ora 30079
30079, 00000, "cannot alter database timezone when database has
TIMESTAMP WITH LOCAL TIME ZONE columns"
// *Cause: An attempt was made to alter database timezone with
// TIMESTAMP WITH LOCAL TIME ZONE column in the database.
// *Action: Either do not alter database timezone or first drop all the
// TIMESTAMP WITH LOCAL TIME ZONE columns.
其原因是:倘若你能修改数据库的时区,就必须将每一个有TIMESTAMP WITH LOCAL TIME ZONE的表
重写,否则在新时区下,它们当前的值将是不正确的!

12.6.4 INTERVAL类型

上一节我们简要地提到了INTERVAL类型。这是表示一段时间或一个时间间隔的一种方法。这一节将 讨论两个INTERVAL类型:其中一个是YEAR TO MONTH类型,它能存储按年和月指定的一个时段;另一个类 型是DATE TO SECOND类型,它能存储按天、小时、分钟和秒(包括小数秒)指定的时段。

在具体介绍这两个INTERVAL类型之前,我想先谈谈EXTRACT内置函数,处理这种类型时这个函数可 能非常有用。EXTRACT内置函数可以处理TIMESTAMP和INTERVAL,并从中返回各部分信息,如从TIMESTAMP 返回时区,从INTERVAL返回小时/天/分钟。还是用前面的例子(其中得到了380天、12.小时、29.878秒 的INTERVAL):
ops$tkyte@ORA10G> select dt2-dt1
2 from (select to_timestamp('29-feb-2000 01:02:03.122000',
3 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('12.-mar-2001 12.:22:33.000000',

5 'dd-mon-yyyy hh24:mi:ss.ff') dt2
6 from dual )
7 / DT2-DT1
---------------------------------------------------------------------------
+000000380 12.:20:29.878000000

可以使用EXTRACT来查看,它能很轻松地取出其中的各部分信息:
ops$tkyte@ORA10G> select extract( day from dt2-dt1 ) day,
2 extract( hour from dt2-dt1 ) hour,
3 extract( minute from dt2-dt1 ) minute,
4 extract( second from dt2-dt1 ) second
5 from (select to_timestamp('29-feb-2000 01:02:03.122000',
6 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
7 to_timestamp('12.-mar-2001 12.:22:33.000000',
8 'dd-mon-yyyy hh24:mi:ss.ff') dt2
9 from dual )
12. /
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
380 12. 20 29.878
另外,我们已经了解了创建 YEAR TO MONTH 和 DAY TO SECOND 间隔时所用的 NUMTOYMINTERVAL 和
NUMTODSINTERVAL。我发现这些函数是创建INTERVAL类型实例最容易的方法,远远胜于串转换函数。我不 喜欢把一大堆表示天、小时、分钟和秒的数连接在一起来表示某个间隔,而是会增加4个NUMTODSINTERVAL 调用来完成同样的工作。
INTERVAL类型不只是可以用于存储时段,还可以以某种方式存储“时间”。例如,如果你希望存储一 个特定的日期时间,可以使用 DATE 或 TIMESTAMP 类型。但是如果你只想存储上午 8:00 这个时间呢? INTERVAL类型就很方便(尤其是INTERVAL DAY TO SECOND类型)。

1. INTERVAL YEAR TO MONTH


INTERVAL YEAR TO MONTH的语法很简单:
INTERVAL YEAR(n) TO MONTH
在此N是一个可选的位数(用以支持年数),可取值为0~9,默认为2(表示年数可为0~99)。这就

允许你存储任意大小的年数(最多可达9位)和月数。我更喜欢用NUMTOYMINTERVAL函数来创建这种类型 的INTERVAL实例。例如,要创建一个5年2个月的时间间隔,可以使用以下命令:
ops$tkyte@ORA10G> select numtoyminterval(5,'year')+numtoyminterval(2,'month')
2 from dual;
NUMTOYMINTERVAL(5,'YEAR')+NUMTOYMINTERVAL(2,'MONTH')
---------------------------------------------------------------------------
+000000005-02

或者,利用1年有12.个月这个事实,可以使用一个调用,并使用以下命令:
ops$tkyte@ORA10G> select numtoyminterval(5*12.+2,'month')
2 from dual;
NUMTOYMINTERVAL(5*12.+2,'MONTH')
---------------------------------------------------------------------------
+000000005-02
这两种方法都能很好地工作。还可以用另一个函数 TO_YMINTERVAL 将一个串转换为一个年/月

INTERVAL类型:
ops$tkyte@ORA10G> select to_yminterval( '5-2' ) from dual;
TO_YMINTERVAL('5-2')
---------------------------------------------------------------------------
+000000005-02
但是,由于我的应用中大多数情况下都是把年和月放在两个 NUMBER 字段中,所以我发现
NUMTOYMINTERVAL函数更有用,而不是先从数字构建一个格式化的串。

最后,还可以直接在SQL中使用INTERVAL类型,而不用这些函数:
ops$tkyte@ORA10G> select interval '5-2' year to month from dual;
INTERVAL'5-2'YEARTOMONTH
---------------------------------------------------------------------------
+05-02

2. INTERVAL DAY TO SECOND


INTERVAL DAY TO SECOND类型的语法很简单:
INTERVAL DAY(n) TO SECOND(m)
在此N是一个可选的位数,支持天数分量,取值为 0~9,默认为2。M是秒字段小时部分中保留的位

数,其中为0~9,默认为6.同样,我更喜欢用NUMTODSINTERVAL函数来创建这种类型的INTERVAL实例:
ops$tkyte@ORA10G> select numtodsinterval( 12., 'day' )+
2 numtodsinterval( 2, 'hour' )+
3 numtodsinterval( 3, 'minute' )+
4 numtodsinterval( 2.3312, 'second' )
5 from dual;
NUMTODSINTERVAL(12.,'DAY')+NUMTODSINTERVAL(2,'HOUR')+NUMTODSINTERVAL(3,'MINU
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

或者只是:
ops$tkyte@ORA10G> select numtodsinterval( 12.*86400+2*3600+3*60+2.3312, 'second' )
2 from dual;
NUMTODSINTERVAL(12.*86400+2*3600+3*60+2.3312,'SECOND')
---------------------------------------------------------------------------
+000000010 02:03:02.331200000
这里利用了一天有89,400秒,一小时有3,600秒等事实。或者,像前面一样,可以使用 TO_DSINTERVAL

函数将一个串转换为一个DAY TO SECOND间隔:
ops$tkyte@ORA10G> select to_dsinterval( '12. 02:03:02.3312' )
2 from dual; TO_DSINTERVAL('1002:03:02.3312')
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

或者只是在SQL本身中使用INTERVAL变量:
ops$tkyte@ORA10G> select interval '12. 02:03:02.3312' day to second
2 from dual; INTERVAL'1002:03:02.3312'DAYTOSECOND
---------------------------------------------------------------------------
+12. 02:03:02.331200