《Oracle编程艺术》学习笔记(36)-数据类型-时间日期

固有数据类型DATE、TIMESTAMP和INTERVAL紧密相关。
DATE和TIMESTAMP存储精度可变的固定日期/时间。INTERVAL可以存储一个时间量。

1）DATE
7字节的定宽日期/时间数据类型。
总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。

2）TIMESTAMP [(fractional_seconds_precision)]
7字节或11字节的定宽日期/时间数据类型。
与DATE相比，还可以包含0~9小数位秒（fractional second）。默认6个小数位。

3）TIMESTAMP[(fractional_seconds)] WITH TIME ZONE
13字节的定宽TIMESTAMP，还提供了时区（TIME ZONE）支持。数据中会随TIMESTAMP存储有关时区的额外信息。

4）TIMESTAMP WITH LOCAL TIME ZONE
与TIMESTAMP类似，7字节或11字节的定宽日期/时间数据类型。
存入数据库时，把数据中的日期/时间部分转换为按照数据库时区表示的日期/时间。
获取数据时，把数据中的日期/时间部分转换为按照会话的时区表示的日期/时间。

5）INTERVAL[(year_precision)] YEAR TO MONTH
5字节的定宽数据类型，用于存储一个时间段，这个类型将时段存储为年数和月数。
year_precision可以为0~9个数字，默认值2。
可以在日期运算中使用这种时间间隔使一个DATE 或TIMESTAMP类型增加或减少一段时间。

6）INTERVAL DAY[(day_precision)] TO SECOND [(fractional_seconds)]
11字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为天/小时/分钟/秒数。
day_precision可以为0~9个数字，默认值2。
fractional_seconds可以为0~9个数字，默认值6。

将一个表示DATE、TIMESTAMP 或INTERVAL的串发送到数据库时就应该指定格式。不要依赖于默认格式。

利用TRUNC函数截断DATE

tony@ORA11GR2> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

tony@ORA11GR2> create table t (x date, y varchar2(32));
Table created.

tony@ORA11GR2> insert into t values(sysdate, sysdate);
1 row created.

tony@ORA11GR2> select trunc(x, 'yyyy'), y from t;
TRUNC(X,'YYYY')     Y
------------------- --------------------------------
2011-01-01 00:00:00 2011-10-27 17:13:23

tony@ORA11GR2> select trunc(x, 'mm'), y from t;
TRUNC(X,'MM')       Y
------------------- --------------------------------
2011-10-01 00:00:00 2011-10-27 17:13:23

tony@ORA11GR2> select trunc(x, 'mi'), y from t;
TRUNC(X,'MI')       Y
------------------- --------------------------------
2011-10-27 17:13:00 2011-10-27 17:13:23

向DATE加减时间

-----------------------------------------------------------------------
时间单位     操           作
-----------------------------------------------------------------------
n秒        DATE+n/24/60/60       或者  DATE+NUMTODSINTERVAL(n,'second')
n分钟      DATE+n/24/60          或者  DATE+NUMTODSINTERVAL(n,'minute')
n小时      DATE+n/24             或者  DATE+NUMTODSINTERVAL(n,'hour')
n天        DATE+n
n周        DATE+n*7
n月        ADD_MONTHS(DATE,n)    或者  DATE+NUMTOYMINTERVAL(n,'month')
n年        ADD_MONTHS(DATE,n*12) 或者  DATE+NUMTOYMINTERVAL(n,'year')
-----------------------------------------------------------------------

需要注意，一般应该使用ADD_MONTHS来处理年和月，而不要使用NUMTOYMINTERVAL，因为这2个函数处理月末日期的行为不一样。

tony@ORA11GR2> select dt, add_months(dt, 1)
  2  from (select to_date('2000-02-29', 'yyyy-mm-dd') dt from dual);

DT                  ADD_MONTHS(DT,1)
------------------- -------------------
2000-02-29 00:00:00 2000-03-31 00:00:00

tony@ORA11GR2> select dt, add_months(dt, 1)
  2  from (select to_date('2000-03-31', 'yyyy-mm-dd') dt from dual);

DT                  ADD_MONTHS(DT,1)
------------------- -------------------
2000-03-31 00:00:00 2000-04-30 00:00:00

tony@ORA11GR2> select dt, dt+numtoyminterval(1,'month')
  2  from (select to_date('2000-02-29', 'yyyy-mm-dd') dt from dual);

DT                  DT+NUMTOYMINTERVAL(
------------------- -------------------
2000-02-29 00:00:00 2000-03-29 00:00:00

tony@ORA11GR2> select dt, dt+numtoyminterval(1,'month')
  2  from (select to_date('2000-03-31', 'yyyy-mm-dd') dt from dual);
select dt, dt+numtoyminterval(1,'month')
             *
ERROR at line 1:
ORA-01839: date not valid for month specified

tony@ORA11GR2> select dt, dt+numtoyminterval(1,'year')
  2  from (select to_date('2000-02-29', 'yyyy-mm-dd') dt from dual);
select dt, dt+numtoyminterval(1,'year')
             *
ERROR at line 1:
ORA-01839: date not valid for month specified

计算两个DATE之差
1）两个DATE相减得到相隔天数（包括小数）
2）使用MONTHS_BETWEEN函数得到相隔月数（包括小数）
3）使用INTERVAL类型可以查看2个日期之间的时间
例如，计算2个日期之间相隔的年数、月数、天数、小时数、分钟数和秒数。

tony@ORA11GR2> column years_months format a32;
tony@ORA11GR2> column days_hours format a32;
tony@ORA11GR2> select numtoyminterval(trunc(months_between(dt2,dt1)), 'month')
  2  years_months,
  3  numtodsinterval(dt2-add_months(dt1, trunc(months_between(dt2,dt1))), 'day')
  4  days_hours
  5  from (select to_date('2000-02-29 00:01:02', 'yyyy-mm-dd hh24:mi:ss') dt1,
  6               to_date('2002-02-01 01:02:01', 'yyyy-mm-dd hh24:mi:ss') dt2
  7        from dual)
  8  /

YEARS_MONTHS                     DAYS_HOURS
-------------------------------- --------------------------------
+000000001-11                    +000000001 01:00:59.000000000

向TIMESTAMP 加减时间
DATE执行日期算术运算所用的方法适用于TIMESTAMP，但是直接加减数字和add_months会导致TIMESTAMP到DATE的隐式转换。
所以一般使用INTERVAL来进行时间加减，但是需要注意月末日期可能导致的问题。

tony@MYTEST2> select systimestamp, systimestamp+1/24 from dual;

SYSTIMESTAMP                             SYSTIMESTAMP+1/24
---------------------------------------- -------------------
28-OCT-11 01.33.34.090000 PM +08:00      2011-10-28 14:33:34

tony@MYTEST2> select systimestamp, add_months(systimestamp,1) from dual;

SYSTIMESTAMP                             ADD_MONTHS(SYSTIMES
---------------------------------------- -------------------
28-OCT-11 01.33.36.606000 PM +08:00      2011-11-28 13:33:36

tony@MYTEST2> select systimestamp, systimestamp+numtoyminterval(1,'month') from dual;

SYSTIMESTAMP                          SYSTIMESTAMP+NUMTOYMINTERVAL(1,'MONTH')
------------------------------------- -------------------------------------
28-OCT-11 01.37.21.920000 PM +08:00   28-NOV-11 01.37.21.920000000 PM +08:00

计算两个TIMESTAMP之差
这一点上，DATE和TIMESTAMP类型存在显著差异。
将DATE相减的结果是一个NUMBER（间隔天数），但TIMESTAMP相减的结果却是一个INTERVAL。

tony@MYTEST2> declare
  2    l_past_time timestamp;
  3    l_current_time timestamp;
  4  begin
  5    select systimestamp into l_past_time from dual;
  6    dbms_lock.sleep(1);
  7    select systimestamp into l_current_time from dual;
  8    dbms_output.put_line(l_current_time - l_past_time);
  9  end;
 10  /
+000000000 00:00:01.000000000

PL/SQL procedure successfully completed.

支持时区（TIME ZONE）的TIMESTAMP类型
有两种支持时区的TIMESTAMP类型：TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE。
TIMESTAMP WITH TIME ZONE类型存储的时候保留了时区信息，TIMESTAMP WITH LOCAL TIME ZONE则根据会话时区和数据库时区，将时间转换为数据库时区后进行存储，读取时则转换为相应的会话时区表示的时间。
如果不需要记住源时区，只需要这样一种数据类型，要求能对日期/时间类型提供一致的全球性处理，那么TIMESTAMP WITH LOCAL TIME ZONE 对大多数应用来说已经能提供足够的支持。

数据库时区可以在创建数据库时用set time_zone=...子句来指定。默认是+00：00。
可以用alter session/database set time_zone=...语句来更改时区。
可以用以下语句查询数据库时区和会话时区。

tony@MYTEST2> select dbtimezone, sessiontimezone from dual;
DBTIME SESSIONTIMEZONE
------ --------------------
+00:00 +08:00

下面的例子可以看出来这两种类型的区别。

tony@MYTEST2> create table t(
  2  ts timestamp,
  3  ts_timezone timestamp with time zone,
  4  ts_local timestamp with local time zone);
Table created.

tony@MYTEST2> insert into t values(
  2    to_timestamp('2000-02-29 00:00:00.000', 'yyyy-mm-dd hh24:mi:ss.ff'),
  3    to_timestamp_tz('2000-02-29 00:00:00.000 +08:00', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
  4    to_timestamp('2000-02-29 00:00:00.000', 'yyyy-mm-dd hh24:mi:ss.ff'));
  1 row created.
  
tony@MYTEST2> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
Session altered.

tony@MYTEST2> alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff tzh:tzm';
Session altered.

tony@MYTEST2> select * from t;
TS                             TS_TIMEZONE
------------------------------ ----------------------------------------
TS_LOCAL
------------------------------
2000-02-29 00:00:00.000000     2000-02-29 00:00:00.000000 +08:00
2000-02-29 00:00:00.000000

tony@MYTEST2> alter session set time_zone='+7:00';
Session altered.

tony@MYTEST2> select * from t;
TS                             TS_TIMEZONE
------------------------------ ----------------------------------------
TS_LOCAL
------------------------------
2000-02-29 00:00:00.000000     2000-02-29 00:00:00.000000 +08:00
2000-02-28 23:00:00.000000

此外，一旦在数据库中创建了包含TIMESTAMP WITH LOCAL TIME ZONE类型数据的表格，并插入了数据，就不能更改数据库时区了。
因为如果可以修改，在新时区下，无法得到正确时间。

tony@MYTEST2> conn / as sysdba
Connected.
sys@MYTEST2> alter database set time_zone='+08:00';
alter database set time_zone='+08:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

INTERVAL YEAR TO MONTH类型
可以使用以下办法创建INTERVAL YEAR TO MONTH类型的实例
1）NUMTOYMINTERVAL 函数
2) TO_YMINTERVAL函数
3) 直接在SQL中使用INTERVAL类型

例如，

tony@MYTEST2> select numtoyminterval(1,'year')
  2  + numtoyminterval(2,'month') from dual;

NUMTOYMINTERVAL(1,'YEAR')+NUMTOYMINTERVAL(2,'MONTH')
----------------------------------------------------
+000000001-02

tony@MYTEST2> select to_yminterval('1-2') from dual;

TO_YMINTERVAL('1-2')
----------------------------------------------------
+000000001-02

tony@MYTEST2> select interval '5-2' year to month from dual

INTERVAL'5-2'YEARTOMONTH
-----------------------------------------------------------
+05-02

INTERVAL DAY TO SECOND类型
可以使用以下办法创建INTERVAL DAY TO SECOND类型的实例
1）NUMTODSINTERVAL 函数
2) TO_DSINTERVAL函数
3) 直接在SQL中使用INTERVAL类型

tony@MYTEST2> select numtodsinterval(1,'day')
  2  + numtodsinterval(12,'hour') from dual;

NUMTODSINTERVAL(1,'DAY')+NUMTODSINTERVAL(12,'HOUR')
-----------------------------------------------------------------------
+000000001 12:00:00.000000000

tony@MYTEST2> select to_dsinterval('1 12:10:10.123') from dual;

TO_DSINTERVAL('112:10:10.123')
-----------------------------------------------------------------------
+000000001 12:10:10.123000000

tony@MYTEST2> select interval '1 12:10:10.123' day to second from dual;

INTERVAL'112:10:10.123'DAYTOSECOND
-----------------------------------------------------------------------
+01 12:10:10.123000

EXTRACT 函数
可以使用EXTRACT函数从DATE, TIMESTAMP 和INTERVAL中返回各部分信息。
EXTRACT(
{ YEAR | MONTH | DAY |HOUR | MINUTE | SECOND } |
{ TIMEZONE_HOUR |TIMEZONE_MINUTE } |
{ TIMEZONE_REGION }|
TIMEZONE_ABBR }
FROM x)
例如：

tony@MYTEST2> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2011

tony@MYTEST2> select extract(timezone_hour from systimestamp) from dual;

EXTRACT(TIMEZONE_HOURFROMSYSTIMESTAMP)
--------------------------------------
                                     8