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

最新推荐文章于 2023-06-09 10:51:03 发布

iteye_1315

最新推荐文章于 2023-06-09 10:51:03 发布

阅读量119

点赞数

文章标签：数据库

固有数据类型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