Oracle 中的日期类型及其相关的函数
Oracle 提供了很多的内置的日期类型,包括如下几种:
Date
Timestamp
Timestamp With Time Zone
Timestamp With Local Time Zone
Interval Year To Month
Interval Day To Second
Date类型用于存储日期,精确到秒,可能通过设置NLS_DATE_FORMAT或者TO_CHAR方法来格式化日期的输出。
Timestamp用于存在日期与时间信息,这个类型比Date更精确,最小能精确到小数点后9位。下面是一个例子:
scott@ORCL> select nowDate,nowTimestamp from testtime;
NOWDATE NOWTIMESTAMP
--------- -------------------------------
24-NOV-08 24-NOV-08 10.36.59.000000000 PM
Timestamp With Time Zone类型与Timestamp非常相似,只是是按照指定时区进行显示。
Timestamp With Local Time Zone 类型以上面的更加相似,这里是按照当前时区显示时间。
下面是一个例子:
scott@ORCL>select * from testtime;
NOWDATE NOWTIMESTAMP TIMEWITHZONE TIMEWITHLOCALZONE
--------- ----------------------------------- -----------------------------------
24-NOV-08 24-NOV-08 10.44.55.000000000 PM 24-NOV-08 10.44.55.000000 PM +08:00 24-NOV-08 10.44.55.000000 PM
可以使用ALTER SESSION SET来设置Timestamp的输出,如:
scott@ORCL> ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD HH24:MI:SS';
Session altered.
scott@ORCL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT
= 'YYYY-MM-DD HH24:MI:SS.FF';
Session altered.
scott@ORCL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT
= 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM';
Session altered.
scott@ORCL>/
NOWDATE NOWTIMESTAMP TIMEWITHZONE TIMEWITHLOCALZONE
------------------- ------------------------------ ----------------------------------- ------------------------
2008-11-24 22:44:55 2008-11-24 22:44:55.000000000 2008-11-24 22:44:55.000000+08:00 2008-11-24 22:44:55.000000
Interval Year[(precision)] To Month类型用来表示一段以年和月为单位时间,主要用来表达两个时间之间的间隔,如1年又3个月。在year上面可以设定一个精确度,取值为0-9,默认的值为2。
Interval Day[(precision)] To Second 与上面的类型类似,主要用来表示以天,小时,分钟,秒为单位的一段时间。在day上面可以设定一个精确度,取值为0-9,不设定时默认为6。
这两个Interval类型可以进行加,减,乘,除运算。下面通过两个例子来看看这两种类型如何工作:
scott@ORCL>create table test_interval(ts1 timestamp(2),y2m interval year(4) to month,d2s interval day(4) to second);
Table created
scott@ORCL>insert into test_interval values(systimestamp,to_YMInterval('4-7'),to_DSInterval('5 02:20:30.30));
scott@ORCL> select * from test_interval;
TS1 Y2M D2S
------------------------- ---------- ------------------------------
2008-11-24 23:06:10.71 +0004-07 +0005 02:20:30.300000
通过上面例子可以看到,to_YMInterval()和 to_DSInterval()方法能将一个字符串转换成Interval类型
下面这个例子展示一下这个类型的使用方法:
scott@ORCL> select sysdate ,sysdate +t.y2m,sysdate +t.d2s from test_interval t;
SYSDATE SYSDATE +T.Y2M SYSDATE +T.D2S
------------------- ------------------- -------------------
2008-11-24 23:25:02 2013-06-24 23:25:02 2008-11-29 01:45:32
从上面的查询可以看到,sysdate +t.y2m在当时时间上增加了4年又7个月,sysdate +t.d2s在当时时间上增加了5天,2小时,20分,30秒。
下面看看Oracle 提供的与日期相关的函数
Trunc(date [,fmt]) 这个函数用来截取日期,精度由fmt控制
To_Timestamp(var1,fmt) 将字符串转换成Timestamp类型
TO_DATE(var1,fmt) 将字符串转换成Date类型
Add_months(date,n) 增加n个月,为负数时是减少n个月
Last_day(date) 当月的最后一天,可通过与Add_months结合取得其它的月的最后一天
Month_between(date,date) 返回两个时间的相关的月数
Next_day(date,dow) dow表示星期几,返回date的下一个dow的日期
Round(date [,fmt]) 和数据的Round类似,fmt表示Round的精度,默认为天,如HH24,DD等
Extract(var from date) 从date中取出相应的年,月,日,小时,分钟,秒等信息
下面是一些例子:
SQL> select sysdate ,add_months(sysdate ,+1) next_month,add_months(sysdate ,-1) pro_month from dual
SYSDATE NEXT_MONTH PRO_MONTH
-------------------- -------------------- --------------------
25-Nov-2008 22:29:35 25-Dec-2008 22:29:35 25-Oct-2008 22:29:35
SQL> select sysdate ,
2 extract(year from sysdate ) year,
3 extract(month from sysdate ) month,
4 extract(day from sysdate ) day
5* from dual
SYSDATE YEAR MONTH DAY
-------------------- ---------- ---------- ----------
25-Nov-2008 22:32:01 2008 11 25
SQL> select last_day(sysdate ) last_day from dual;
LAST_DAY
---------
30-NOV-08
SQL> select last_day(add_months(sysdate ,1)) next_last_day from dual
NEXT_LAST_DAY
--------------------
31-Dec-2008 22:46:25
SQL> select sysdate ,round(sysdate ,'HH24') from dual;
SYSDATE ROUND(SYSDATE ,'HH24'
-------------------- --------------------
25-Nov-2008 22:47:23 25-Nov-2008 23:00:00