Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。DATE和TIMESTAMP类型存储精度可变的固定日期/时间。INTERVAL类型可以很容易地存储一个时间量，如“8个小时”或“30天”。
许多新应用都在使用TIMESTAMP类型，这有两个原因：一方面它支持小数秒，而DATE类型不支持；另一方面TIMESTAMP类型支持时区，这也是DATE类型力所不能及的。
1格式
以某种格式对数据库中的数据进行格式化，以满足你的要求。
告诉数据库如何将一个输入串转换为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日。现在，假设有人认为正确而且适当的日期格式应该是MM/DD/YY。突然之间，原来的日期就会变成2003年1月2日。或者有人认为YY/MM/DD才对，现在的日期就会变成2001年2月3日。简单地说，如果日期串没有带相应的日期格式，就会有多种解释方法。这个INSERT语句最好写作：
Insert into t ( date_column ) values ( to_date( '01/02/2003', 'DD/MM/YYYY' ) );
使用一个4字符的年份。
从数据库取出的数据也同样存在上述问题。如果你执行SELECT DATE_COLUMN FROM T，并在应用中把这一列获取到一个串中，就应该对其应用一个显示的日期格式。不论你的应用期望何种格式，都应该在这里显式指定。否则，如果将来某个时刻有人修改了默认日期格式，你的应用就可能会崩溃，或者有异样的表现。
2 DATE类型
DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。Oracle使用一种内部格式来表示这个信息。
1scott@ORCL>create table t ( x date ); 2 3表已创建。 4 5scott@ORCL>alter session set nls_date_language='american' ; 6 7会话已更改。 8 9scott@ORCL>insert into t (x) values 10 2 ( to_date( '25-jun-2005 12:01:00', 11 3 'dd-mon-yyyy hh24:mi:ss' ) ); 12 13已创建 1 行。 14 15scott@ORCL>select x, dump(x) d from t; 16 17X D 18----------- ---------------------- 1925-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
世纪和年份字节（DUMP输出中的120,105）采用一种“加100”（excess-100）表示法来存储。必须将其减去100来确定正确的世纪和年份。之所以采用加100表示法，这是为了支持BC和AD日期。如果从世纪字节减去100得到一个负数，则是一个BC日期，例如：
1scott@ORCL>insert into t (x) values 2 2 ( to_date( '01-jan-4712bc', 3 3 'dd-mon-yyyybc hh24:mi:ss' ) ); 4 5已创建 1 行。 6 7scott@ORCL>select x, dump(x) d from t; 8 9X D 10--------- ---------------------------------- 1125-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1 1201-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
因此，插入01-JAN-4712BC时，世纪字节是53，而53-100=-47，这才是我们插入的真实世纪。由于这是一个负数，所以我们知道它是一个BC日期。这种存储格式还允许日期以一种二进制方式自然地排序。由于4712 BC小于4710 BC，我们希望能有一种支持这种顺序的二进制表示。通过转储这两个日期，可以看到01-JAN-4710BC比4712 BC中的同一天“更大”，所以它们确实能正确地排序，并很好地进行比较：
1scott@ORCL>insert into t (x) values 2 2 ( to_date( '01-jan-4710bc', 3 3 'dd-mon-yyyybc hh24:mi:ss' ) ); 4 5已创建 1 行。 6 7scott@ORCL>select x, dump(x) d from t; 8 9X D 10------------ ---------------------------------- 1125-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1 1201-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1 1301-JAN-10 Typ=12 Len=7: 53,90,1,1,1,1,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，并执行以下插入：
1scott@ORCL>create table t ( what varchar2(12), x date ); 2 3表已创建。 4 5scott@ORCL>insert into t (what, x) values 6 2 ( 'orig', to_date( '25-jun-2005 12:01:00','dd-mon-yyyy hh24:mi:ss' ) ); 7 8已创建 1 行。 9 10scott@ORCL>insert into t (what, x) 11 2 select 'minute', trunc(x,'mi') from t 12 3 union all 13 4 select 'day', trunc(x,'dd') from t 14 5 union all 15 6 select 'month', trunc(x,'mm') from t 16 7 union all 17 8 select 'year', trunc(x,'y') from t 18 9 / 19 20已创建4行。 21 22scott@ORCL>select what, x, dump(x,12) d from t; 23 24WHAT X 25------------------------ ------------ 26D 27-------------------------------------------------------------------------------- 28-------------------------------------------------------------------------------- 29---------------------------------------- 30orig 25-JUN-05 31Typ=12 Len=7: 120,105,6,25,13,2,1 32 33minute 25-JUN-05 34Typ=12 Len=7: 120,105,6,25,13,2,1 35 36day 25-JUN-05 37Typ=12 Len=7: 120,105,6,25,1,1,1 38 39month 01-JUN-05 40Typ=12 Len=7: 120,105,6,1,1,1,1 41 42year 01-JAN-05 43Typ=12 Len=7: 120,105,1,1,1,1,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列：
1scott@ORCL>create table t 2 2 as 3 3 select created from all_objects; 4 5表已创建。 6 7scott@ORCL>exec dbms_stats.gather_table_stats( user, 'T' ); 8 9PL/SQL 过程已成功完成。
然后，启用SQL_TRACE，我们反复使用上述两种技术查询这个表，可以看到以下结果：
select count(*)
from
t where to_char(created,'yyyy') = '2005'
select count(*)
from
t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy')
可以看到存在明显的差异。与使用TRUNC相比，使用TO_CHAR所用的CPU时间与前者相差一个数量级（即相差12倍）。这是因为TO_CHAR必须把日期转换为一个串，这要使用一个更大的代码路径，并利用当前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面，TRUNC只需把后5个字节设置为1。然后将两个7字节的二进制数进行比较，就大功告成了。因此，如果只是要截断一个DATE列，应该避免使用TO_CHAR。
另外，甚至要尽可能完全避免对DATE列应用函数。把前面的例子 可以看到其目标是获取2005年的所有数据。那好，如果CREATED上有一个索引，而且表中只有很少一部分CREATED值是2005年的时间，会怎么样呢？我们可能希望能够使用这个索引，为此要使用一个简单的谓词来避免在数据库列上应用函数：
1select count(*) from t 2where created >= to_date('01-jan-2005','dd-mon-yyyy') 3and created < to_date('01-jan-2006','dd-mon-yyyy');
这样有两个好处：
这样一来就可以考虑使用CREATED上的索引了。
根本无需调用TRUNC函数，这就完全消除了相应的开销。
这里使用的是区间比较而不是TRUNC或TO_CHAR，这种技术同样适用于TIMESTAMP类型。如果能在查询中避免对一个数据库列应用函数，就应该全力这样做。一般来讲，避免使用函数会有更好的性能，而且允许优化器在更多的访问路径中做出选择。
1. 向DATE增加或减去时间
常用的技术有3种：
向DATE增加一个NUMBER。把DATE加1是增加1天的一种方法。因此，向DATE增加12.24就是增加1个小时，依此类推。
使用INTERVAL类型来增加时间单位。INTERVAL类型支持两种粒度：年和月，或日/小时/分钟/秒。
使用内置的ADD_MONTHS函数增加月。
总的来讲，使用Oracle DATE类型时 建议：
使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。
加一个简单的数来增加天。
使用ADD_MONTHS内置函数来增加月和年。
我建议不要使用NUMTOYMINTERVAL函数。其原因与这个函数如何处理月末日期有关。
ADD_MONTHS函数专门处理月末日期。它实际上会为我们完成日期的“舍入”。
1scott@ORCL>alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; 2 3会话已更改。 4 5 6scott@ORCL>select to_date('29-feb-2000','dd-mon-yyyy') dt from dual; 7select to_date('29-feb-2000','dd-mon-yyyy') dt from dual 8 * 9第 1 行出现错误: 10ORA-01843: 无效的月份 11 12 13scott@ORCL>alter session set nls_date_language='american' ; 14 15会话已更改。 16 17scott@ORCL>select dt, add_months(dt,1) 18 2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual ) 19 3 / 20 21DT ADD_MONTHS(DT,1) 22----------------------- ----------------------- 2329-feb-2000 00:00:00 31-mar-2000 00:00:00 24 25scott@ORCL>select dt, add_months(dt,1) 26 2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual ) 27 3 / 28 29DT ADD_MONTHS(DT,1) 30----------------------- ----------------------- 3128-feb-2001 00:00:00 31-mar-2001 00:00:00 32 33scott@ORCL>select dt, add_months(dt,1) 34 2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual ) 35 3 / 36 37DT ADD_MONTHS(DT,1) 38----------------------- ----------------------- 3930-jan-2001 00:00:00 28-feb-2001 00:00:00 40 41scott@ORCL>select dt, add_months(dt,1) 42 2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual ) 43 3 / 44 45DT ADD_MONTHS(DT,1) 46----------------------- ----------------------- 4730-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日）。
如果与增加一个间隔 相比较，会看到完全不同的结果：
1scott@ORCL>select dt, dt+numtoyminterval(1,'month') 2 2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual ) 3 3 / 4 5DT DT+NUMTOYMINTERVAL(1,'M 6----------------------- ----------------------- 729-feb-2000 00:00:00 29-mar-2000 00:00:00 8 9scott@ORCL>select dt, dt+numtoyminterval(1,'month') 10 2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual ) 11 3 / 12 13DT DT+NUMTOYMINTERVAL(1,'M 14----------------------- ----------------------- 1528-feb-2001 00:00:00 28-mar-2001 00:00:00
得到的日期并不是下一个月的最后一天，而只是下一个月的同一天。
1scott@ORCL>select dt, dt+numtoyminterval(1,'month') 2 2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual ) 3 3 / 4select dt, dt+numtoyminterval(1,'month') 5 * 6第 1 行出现错误: 7ORA-01839: 指定月份的日期无效 8 9 10scott@ORCL>select dt, dt+numtoyminterval(1,'month') 11 2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual ) 12 3 / 13select dt, dt+numtoyminterval(1,'month') 14 * 15第 1 行出现错误: 16ORA-01839: 指定月份的日期无效
2. 得到两个日期之差
MONTHS_BETWEEN，它会返回表示两个日期相隔月数的一个数（包括月小数）。
利用INTERVAL类型，用另一个方法来查看两个日期之间的逝去时间。
1scott@ORCL>select dt2-dt1 , 2 2 months_between(dt2,dt1) months_btwn, 3 3 numtodsinterval(dt2-dt1,'day') days, 4 4 numtoyminterval(months_between(dt2,dt1),'month') months 5 5 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1, 6 6 to_date('12-mar-2001 12:22:33',' 7dd-mon-yyyy hh24:mi:ss') dt2 8 7 from dual ); 9 10 DT2-DT1 MONTHS_BTWN DAYS 11 MONTHS 12---------- ----------- --------------------------------------------------------- 13------------------ ------------------------------------------------------------- 14-------------- 15377.472569 12.4668571 +000000377 11:20:30.000000000 16 +000000001-00
这些都是“正确”的值，但是对我们来说都没有大用。大多数应用都更愿意显示日期之间相隔的年数、月数、天数、小时数、分钟数和秒数。通过使用前述函数的一个组合，就可以实现这个目标。我们将选出两个间隔：一个是年和月间隔，另一个是日/小时/分钟/秒间隔。我们使用MONTHS_BETWEEN内置函数来确定两个日期之间相隔的月数（包括小数），然后使用NUMTOYMINTERVAL内置函数将这个数转换为年数和月数。另外，使用TRUNC得到两个日期相隔月数中的整数部分，再使用ADD_MONTHS内置函数将dt1增加12个月（这会得到‘28-feb-2001 01:02:03），再从两个日期中的较大者（dt2）减去这个计算得到的日期，从而得到两个日期之间的天数和小时数：
1scott@ORCL>select numtoyminterval(months_between(dt2,dt1),'month') years_months, 2 3 2 numtodsinterval(dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),'day' 4 ) days_hours 5 3 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1, 6 4 to_date('12-mar-2001 12:22:33','dd-mon-yyyy hh24:mi:ss') dt2 7 5 from dual ) ; 8 9YEARS_MONTHS DAYS_HOURS 10------------ ----------------------------- 11+000000001-00 +000000012 11:20:30.000000000
现在就很清楚了，这两个日期之间相隔1年、12天、11小时、20分钟和30秒。
3 TIMESTAMP类型
TIMESTAMP类型与DATE非常类似，只不过另外还支持小数秒和时区。
1. TIMESTAMP
TIMESTAMP(n)
这里N是可选的，用于指定TIMESTAMP中秒分量的小数位数，可以取值为0～9.如果指定0，TIMESTAMP在功能上则与DATE等价，它们实际上会以同样的方式存储相同的值：
1scott@ORCL>create table t 2 2 ( dt date, 3 3 ts timestamp(0) 4 4 ) 5 5 / 6 7表已创建。 8 9scott@ORCL>insert into t values ( sysdate, systimestamp ); 10 11已创建 1 行。 12 13 14scott@ORCL>select dump(dt,12) dump, dump(ts,12) dump from t; 15 16DUMP 17-------------------------------------------------------------------------------- 18-------------------------------------------------------------------------------- 19---------------------------------------- 20DUMP 21-------------------------------------------------------------------------------- 22-------------------------------------------------------------------------------- 23---------------------------------------- 24Typ=12 Len=7: 120,118,8,29,16,30,6 25Typ=180 Len=7: 120,118,8,29,16,30,6
这两个数据类型是不同的（由TYP=字段可知），但是它们采用了相同的方式存储数据。
如果指定要保留几位秒小数，TIMESTAMP数据类型与DATE类型的长度将会不同，例如：
1scott@ORCL>create table t 2 2 ( dt date, 3 3 ts timestamp(9) 4 4 ) 5 5 / 6 7表已创建。 8 9scott@ORCL>insert into t values ( sysdate, systimestamp ); 10 11已创建 1 行。 12 13scott@ORCL>select dump(dt,12) dump, dump(ts,12) dump from t; 14 15DUMP 16-------------------------------------------------------------------------------- 17-------------------------------------------------------------------------------- 18---------------------------------------- 19DUMP 20-------------------------------------------------------------------------------- 21-------------------------------------------------------------------------------- 22---------------------------------------- 23Typ=12 Len=7: 120,118,8,29,16,32,51 24Typ=180 Len=11: 120,118,8,29,16,32,51,52,39,112,192
现在TIMESTAMP占用12 字节的存储空间，最后额外的4个字节包含着小数秒，通过查看所存储的时间就能看出：
1scott@ORCL>alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; 2会话已更改。 3 4scott@ORCL>alter session set nls_date_language='american' ; 5会话已更改。 6 7scott@ORCL>select * from t; 8DT TS 9----------------------- -------------------------------------------------------- 1029-aug-2018 15:31:50 29-AUG-18 03.31.50.875000000 PM 11 12 13scott@ORCL>select dump(ts) dump from t; 14DUMP 15---------------------------------------- 16Typ=180 Len=11: 120,118,8,29,16,32,51,52,39,112,192
存储的小数秒都在最后4个字节中。
2. 向TIMESTAMP增加或减去时间
DATE执行日期算术运算所用的技术同样适用于TIMESTAMP，但是在很多情况下，TIMESTAMP会转换为一个DATE，例如：
1scott@ORCL>alter session set nls_date_language='american' ; 2 3会话已更改。 4 5scott@ORCL>alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; 6 7会话已更改。 8 9scott@ORCL>select systimestamp ts, systimestamp+1 dt 10 2 from dual; 11 12TS DT 13----------------------------------- ----------------------- 1429-AUG-18 04.01.17.096000 PM +08:00 30-aug-2018 16:01:17
这里加1实际上将SYSTIMESTAMP推进了1天，但是小数秒没有了，另外时区信息也没有了。
这里使用 INTERVAL 更有意义：
1scott@ORCL>select systimestamp ts, systimestamp +numtodsinterval(1,'day') dt 2 2 from dual; 3 4TS DT 5----------------------------------- --------------------------------------------------------- 629-AUG-18 04.02.33.960000 PM +08:00 30-AUG-18 04.02.33.960000000 PM +08:00
使用返回一个INTERVAL类型的函数能保持TIMESTAMP的真实度。使用TIMESTAMP时要特别当心，以避免这种隐式转换。
向TIMESTAMP增加月间隔或年间隔时存在相关的警告。如果所得到的“日期”不是一个合法日期，这个操作就会失败。
3. 得到两个TIMESTAMP之差
将DATE相减的结果是一个NUMBER，但TIMESTAMP相减的结果却是一个INTERVAL：
1scott@ORCL>select dt2-dt1 2 2 from (select to_timestamp('29-feb-2000 01:02:03.122000', 3 3 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 4 4 to_timestamp('12-mar-2001 12:22:33.000000', 5 5 'dd-mon-yyyy hh24:mi:ss.ff') dt2 6 6 from dual ) 7 7 / 8 9DT2-DT1 10--------------------------------------------------------------------------- 11+000000377 11:20:29.878000000
两个TIMESTAMP值之差是一个INTERVAL，而且这里显示了二者之间相隔的天数已经小时/分钟/秒数。如果想得到二者之间相差的年数和月数，可以使用以下查询（这个查询类似于先前用于日期的查询）：
1scott@ORCL>select numtoyminterval 2 2 (months_between(dt2,dt1),'month') 3 3 years_months, 4 4 dt2-add_months(dt1,trunc(months_between(dt2,dt1))) 5 5 days_hours 6 6 from (select to_timestamp('29-feb-2000 01:02:03.122000', 7 7 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 8 8 to_timestamp('12-mar-2001 12:22:33.000000', 9 9 'dd-mon-yyyy hh24:mi:ss.ff') dt2 10 10 from dual ) 11 11 / 12 13YEARS_MONTHS DAYS_HOURS 14------------------------- --------------------- 15+000000001-00 +000000012 11:20:30.000000000
在这种情况下，由于使用了ADD_MONTHS，DT1会隐式转换为一个DATE类型，这样就丢失了小数秒。为了保住小数秒，我们必须编写更多的代码。
4. TIMESTAMP WITH TIME ZONE类型
TIMESTAMP WITH TIME ZONE类型继承了TIMESTAMP类型的所有特点，并增加了时区支持。TIMESTAMP WITH TIME ZONE类型占12字节的存储空间，在此有额外的2个字节用于保留时区信息。它在结构上与TIMESTAMP的差别只是增加了这2个字节：
1scott@ORCL>create table t 2 2 ( 3 3 ts timestamp, 4 4 ts_tz timestamp with time zone 5 5 ) 6 6 / 7 8表已创建。 9 10scott@ORCL>insert into t ( ts, ts_tz ) 11 2 values ( systimestamp, systimestamp ); 12 13已创建 1 行。 14 15scott@ORCL>select * from t; 16 17TS TS_TZ 18----------------------------- ------------------------------ 1929-AUG-18 05.54.07.695000 PM 29-AUG-18 05.54.07.695000 PM +08:00 20 21scott@ORCL>select dump(ts), dump(ts_tz) from t; 22 23DUMP(TS) 24---------------------------------------- 25DUMP(TS_TZ) 26---------------------------------------- 27Typ=180 Len=11: 120,118,8,29,18,55,8,41,108,219,192 28Typ=181 Len=13: 120,118,8,29,10,55,8,41,108,219,192,28,60
获取数据时，默认的TIMESTAMP WITH TIME ZONE格式包括有时区信息。
存储数据时，TIMESTAMP WITH TIME ZONE会在数据中存储指定的时区。时区成为数据本身的一部分。注意TIMESTAMP WITH TIME ZONE字段如何存储小时、分钟和秒，这里采用了加1表示法。TIMESTAMP WITH TIME ZONE为它增加了4个小时，从而存储为GWT（也称为UTC）时间。获取时，会使用尾部的2个字节适当地调整TIMESTAMP值。
数据库能存储多个时区的数据：
1scott@ORCL>create table t 2 2 ( ts1 timestamp with time zone, 3 3 ts2 timestamp with time zone 4 4 ) 5 5 / 6 7表已创建。 8 9scott@ORCL>insert into t (ts1, ts2) 10 2 values ( timestamp'2005-06-05 12:02:32.212 US/Eastern', 11 3 timestamp'2005-06-05 12:02:32.212 US/Pacific' ); 12 13 14已创建 1 行。
并对这些数据执行正确的TIMESTAMP运算：
1scott@ORCL>select ts1-ts2 from t; 2 3TS1-TS2 4--------------------------------------------------------------------------- 5-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的精度），但是会进行规范化，在其中存入数据库的时区。
首先，创建一个包括3列的表，这3列分别是一个DATE列、一个TIMESTAMP WITH TIME ZONE列和一个TIMESTAMP WITH LOCAL TIME ZONE列，然后向这3列插入相同的值：
1scott@ORCL>create table t 2 2 ( dt date, 3 3 ts1 timestamp with time zone, 4 4 ts2 timestamp with local time zone 5 5 ) 6 6 / 7 8表已创建。 9 10scott@ORCL>insert into t (dt, ts1, ts2) 11 2 values ( timestamp'2005-06-05 12:02:32.212 US/Pacific', 12 3 timestamp'2005-06-05 12:02:32.212 US/Pacific', 13 4 timestamp'2005-06-05 12:02:32.212 US/Pacific' ); 14 15已创建 1 行。 16 17scott@ORCL>select dbtimezone from dual; 18 19DBTIMEZONE 20------------ 21+00:00
现在，将这些值转储如下：
1scott@ORCL>select dump(dt), dump(ts1), dump(ts2) from t; 2 3DUMP(DT) 4---------------------------------------- 5DUMP(TS1) 6---------------------------------------- 7DUMP(TS2) 8---------------------------------------- 9Typ=12 Len=7: 120,105,6,5,13,3,33 10Typ=181 Len=13: 120,105,6,5,20,3,33,12,162,221,0,137,156 11Typ=231 Len=11: 120,105,6,5,20,3,33,12,162,221,0
可以看到，在这个例子中，会存储3种完全不同的日期/时间表示：
DT：这一列存储了日期/时间。时区和小数秒没有了，因为使用的是DATE类型。这里根本不会执行时区转换。
TS1：这一列保留了TIME ZONE信息，并规范化为该TIME ZONE相应的UTC时间。
TS2：这里认为这个列的时区就是数据库时区。这里采用了加1表示法：取得实际时间时要记住减1。
由于TS1列在最后2字节保留了原来的时区，获取时我们会看到以下结果：
1scott@ORCL>select ts1, ts2 from t; 2 3TS1 TS2 4--------------------------------------- ---------------------------------------------------------- 505-JUN-05 12.02.32.212000 PM US/PACIFIC 06-JUN-05 03.02.32.212000 AM
数据库应该能显示这个信息，但是有LOCAL TIME ZONE（数据库时区）的TS2列只显示了数据库时区的时间，并认为这就是这一列的时区（实际上，这个数据库中有LOCAL TIME ZONE的所有列的时区都是数据库时区）。
如果不需要记住源时区，只需要这样一种数据类型，要求能对日期/时间类型提供一致的全球性处理，那么TIMESTAMP WITH LOCAL TIME ZONE对大多数应用来说已经能提供足够的支持。另外，TIMESTAMP(0) WITH LOCAL TIME ZONE是与DATE类型等价但提供了时区支持的一种类型；它占用7字节存储空间，允许存储按UTC形式“规范化”的日期。
关于TIMESTAMP WITH LOCAL TIME ZONE类型有一个警告，一旦创建有这个列的表，将不能修改数据库的时区。
1scott@ORCL>alter database set time_zone = 'PST'; 2alter database set time_zone = 'PST' 3* 4第 1 行出现错误: 5ORA-30079: 当数据库有 TIMESTAMP WITH LOCAL TIME ZONE 列时不能变更数据库时区
4 INTERVAL类型
这是表示一段时间或一个时间间隔的一种方法。
YEAR TO MONTH类型，它能存储按年和月指定的一个时段；
DATE TO SECOND类型，它能存储按天、小时、分钟和秒（包括小数秒）指定的时段。
EXTRACT内置函数 可以处理TIMESTAMP和INTERVAL，并从中返回各部分信息，如从TIMESTAMP返回时区，从INTERVAL返回小时/天/分钟：
1scott@ORCL>select dt2-dt1 2 2 from (select to_timestamp('29-feb-2000 01:02:03.122000', 3 3 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 4 4 to_timestamp('12-mar-2001 12:22:33.000000', 5 5 'dd-mon-yyyy hh24:mi:ss.ff') dt2 6 6 from dual ) 7 7 / 8 9DT2-DT1 10--------------------------------------------------------------------------- 11+000000377 11:20:29.878000000
可以使用EXTRACT来查看，它能很轻松地取出其中的各部分信息：
1scott@ORCL>select extract( day from dt2-dt1 ) day, 2 2 extract( hour from dt2-dt1 ) hour, 3 3 extract( minute from dt2-dt1 ) minute, 4 4 extract( second from dt2-dt1 ) second 5 5 from (select to_timestamp('29-feb-2000 01:02:03.122000', 6 6 'dd-mon-yyyy hh24:mi:ss.ff') dt1, 7 7 to_timestamp('12-mar-2001 12:22:33.000000', 8 8 'dd-mon-yyyy hh24:mi:ss.ff') dt2 9 9 from dual ) 10 10 / 11 12 DAY HOUR MINUTE SECOND 13---------- ---------- ---------- ---------- 14 377 11 20 29.878
YEAR TO MONTH和DAY TO SECOND间隔时所用的NUMTOYMINTERVAL和NUMTODSINTERVAL,是创建INTERVAL类型实例最容易的方法，远远胜于串转换函数。
INTERVAL类型不只是可以用于存储时段，还可以以某种方式存储“时间”。例如，如果希望存储一个特定的日期时间，可以使用DATE或TIMESTAMP类型。但是如果只想存储上午8:00这个时间呢？INTERVAL类型就很方便（尤其是INTERVAL DAY TO SECOND类型）。
1. INTERVAL YEAR TO MONTH
INTERVAL YEAR(n) TO MONTH
在此N是一个可选的位数（用以支持年数），可取值为0～9，默认为2（表示年数可为0～99）。这就允许你存储任意大小的年数（最多可达9位）和月数。也可以用 NUMTOYMINTERVAL 函数来创建这种类型的INTERVAL实例。
例如，要创建一个5年2个月的时间间隔，可以使用以下命令：
1scott@ORCL>select numtoyminterval(5,'year')+numtoyminterval(2,'month') 2 2 from dual; 3 4NUMTOYMINTERVAL(5,'YEAR')+NUMTOYMINTERVAL(2,'MONTH') 5--------------------------------------------------------------------------- 6+000000005-02
或者，利用1年有12个月这个事实，可以使用一个调用，并使用以下命令：
1scott@ORCL>select numtoyminterval(5*12+2,'month') 2 2 from dual; 3 4NUMTOYMINTERVAL(5*12+2,'MONTH') 5--------------------------------------------------------------------------- 6+000000005-02
可以用另一个函数TO_YMINTERVAL将一个串转换为一个年/月INTERVAL类型：
1scott@ORCL>select to_yminterval( '5-2' ) from dual; 2 3TO_YMINTERVAL('5-2') 4--------------------------------------------------------------------------- 5+000000005-02
还可以直接在SQL中使用INTERVAL类型，而不用这些函数：
1scott@ORCL>select interval '5-2' year to month from dual; 2 3INTERVAL'5-2'YEARTOMONTH 4--------------------------------------------------------------------------- 5+05-02
2. INTERVAL DAY TO SECOND
INTERVAL DAY(n) TO SECOND(m)
在此N是一个可选的位数，支持天数分量，取值为0～9，默认为2。M是秒字段小时部分中保留的位数，其中为0～9，默认为6 同样，可以用 NUMTODSINTERVAL 函数来创建这种类型的INTERVAL实例：
1scott@ORCL>select numtodsinterval( 12, 'day' )+ 2 2 numtodsinterval( 2, 'hour' )+ 3 3 numtodsinterval( 3, 'minute' )+ 4 4 numtodsinterval( 2.3312, 'second' ) 5 5 from dual; 6 7NUMTODSINTERVAL(12,'DAY')+NUMTODSINTERVAL(2,'HOUR')+NUMTODSINTERVAL(3,'MINU 8--------------------------------------------------------------------------- 9+000000012 02:03:02.331200000
或者只是：
1scott@ORCL>select numtodsinterval( 12*86400+2*3600+3*60+2.3312, 'second' ) 2 2 from dual; 3 4NUMTODSINTERVAL(12*86400+2*3600+3*60+2.3312,'SECOND') 5--------------------------------------------------------------------------- 6+000000012 02:03:02.331200000
这里利用了一天有89,400秒，一小时有3,600秒等事实
可以使用TO_DSINTERVAL函数将一个串转换为一个DAY TO SECOND间隔：
1scott@ORCL>select to_dsinterval( '12 02:03:02.3312' ) 2 2 from dual; 3 4TO_DSINTERVAL('1202:03:02.3312') 5--------------------------------------------------------------------------- 6+000000012 02:03:02.331200000
或者只是在SQL本身中使用INTERVAL变量：
1scott@ORCL>select interval '12 02:03:02.3312' day to second 2 2 from dual; 3 4INTERVAL'1202:03:02.3312'DAYTOSECOND 5--------------------------------------------------------------------------- 6+12 02:03:02.331200