Oracle的时间总结 -- oracle知识2016/09/30
来源：互联网 发布：淘宝描述图片尺寸最大 编辑：程序博客网 时间：2024/05/16 23:39
1. Interval 时间
INTERVAL YEAR TO MONTH数据类型
Oracle语法:
INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
该数据类型常用来表示一个时间段，而非其他日期格式表示一个准确的时间，
如： 与sysdate做算法时是以天为单位，如需缩小范围需要/24 /60 /60等， 而是用interval则可直接指定单位并自动换算。
, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2.
eg:
INTERVAL '123-2' YEAR(3) TO MONTH
表示: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.
INTERVAL '123' YEAR(3)
表示: 123年0个月
INTERVAL '300' MONTH(3)
表示: 300个月, 注意该处MONTH的精度是3啊.
INTERVAL '4' YEAR
表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH 是一样的
INTERVAL '50' MONTH
表示: 50个月, 同 INTERVAL '4-2' YEAR TO MONTH 是一样
INTERVAL '123' YEAR
表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL '123' YEAR(3) 或"3"改成大于3小于等于9的数值都可以的
INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH =
INTERVAL '6-11' YEAR TO MONTH
表示: 5年3个月 + 20个月 = 6年11个月
与该类型相关的函数:
NUMTODSINTERVAL(n, 'interval_unit')
将n转换成interval_unit所指定的值, interval_unit可以为: DAY, HOUR, MINUTE, SECOND
注意该函数不可以转换成YEAR和MONTH的.
NUMTOYMINTERVAL(n, 'interval_unit')
interval_unit可以为: YEAR, MONTH
eg: (Oracle Version 9204, RedHat Linux 9.0)
SQL> select numtodsinterval(100,'DAY') from dual;
NUMTODSINTERVAL(100,'DAY')
---------------------------------------------------------------------------
+000000100 00:00:00.000000000
SQL> c/DAY/SECOND
1* select numtodsinterval(100,'SECOND') from dual
SQL> /
NUMTODSINTERVAL(100,'SECOND')
---------------------------------------------------------------------------
+000000000 00:01:40.000000000
SQL> c/SECOND/MINUTE
1* select numtodsinterval(100,'MINUTE') from dual
SQL> /
NUMTODSINTERVAL(100,'MINUTE')
---------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL> c/MINUTE/HOUR
1* select numtodsinterval(100,'HOUR') from dual
SQL> /
NUMTODSINTERVAL(100,'HOUR')
---------------------------------------------------------------------------
+000000004 04:00:00.000000000
SQL> c/HOUR/YEAR
1* select numtodsinterval(100,'YEAR') from dual
SQL> /
select numtodsinterval(100,'YEAR') from dual
*
ERROR at line 1:
ORA-01760: illegal argument for function
SQL> select numtoyminterval(100,'year') from dual;
NUMTOYMINTERVAL(100,'YEAR')
---------------------------------------------------------------------------
+000000100-00
SQL> c/year/month
1* select numtoyminterval(100,'month') from dual
SQL> /
NUMTOYMINTERVAL(100,'MONTH')
---------------------------------------------------------------------------
+000000008-04
时间的计算:
SQL> select to_date('1999-12-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual;
TO_DATE('1999-12-12','YYYY-MM-DD')-TO_DATE('1999-12-01','YYYY-MM-DD')
---------------------------------------------------------------------
11
-- 可以相减的结果为天.
SQL> c/1999-12-12/1999-01-12
1* select to_date('1999-01-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual
SQL> /
TO_DATE('1999-01-12','YYYY-MM-DD')-TO_DATE('1999-12-01','YYYY-MM-DD')
---------------------------------------------------------------------
-323
-- 也可以为负数的
SQL> c/1999-01-12/2999-10-12
1* select to_date('2999-10-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual
SQL> /
TO_DATE('2999-10-12','YYYY-MM-DD')-TO_DATE('1999-12-01','YYYY-MM-DD')
---------------------------------------------------------------------
365193
下面看看INTERVAL YEAR TO MONTH怎么用.
SQL> create table bb(a date, b date, c interval year(9) to month);
Table created.
SQL> desc bb;
Name Null? Type
----------------------------------------- -------- ----------------------------
A DATE
B DATE
C INTERVAL YEAR(9) TO MONTH
SQL> insert into bb values(to_date('1985-12-12', 'yyyy-mm-dd'), to_date('1984-12-01','yyyy-mm-dd'), null)
1 row created.
SQL> select * from bb;
A B
--------- ---------
C
---------------------------------------------------------------------------
12-DEC-85 01-DEC-84
SQL> update bb set c = numtoyminterval(a-b, 'year');
1 row updated.
SQL> select * from bb;
A B
--------- ---------
C
---------------------------------------------------------------------------
12-DEC-85 01-DEC-84
+000000376-00
-- 直接将相减的天变成年了, 因为我指定变成年的
SQL> select a-b, c from bb;
A-B
----------
C
---------------------------------------------------------------------------
376
+000000376-00
SQL> insert into bb values(null,null,numtoyminterval(376,'month'));
1 row created.
SQL> select * from bb;
A B C
--------- --------- --------------------------------------------
12-DEC-85 01-DEC-84 +000000376-00
+000000031-04
SQL> insert into bb values ( null,null, numtoyminterval(999999999,'year'));
1 row created.
SQL> select * from bb;
A B C
--------- --------- ---------------------------------------------------------------------
12-DEC-85 01-DEC-84 +000000376-00
+000000031-04
+999999999-00
========================
今天来添加点新的东西！[2008-07-26] 这部分东东来源：http://www.oraclefans.cn/forum/showblog.jsp?rootid=139
INTERVAL YEAR TO MONTH类型2个TIMESTAMP类型的时间差别。内部类型是182，长度是5。其中4个字节存储年份差异，存储的时候在差异上加了一个0X80000000的偏移量。一个字节存储月份的差异，这个差异加了60的偏移量。
SQL> ALTER TABLE TestTimeStamp ADD E INTERVAL YEAR TO MONTH;
SQL> update testTimeStamp set e=(select interval '5' year + interval '10' month year from dual);
已更新3行。
SQL> commit;
提交完成。
SQL> select dump(e,16) from testTimeStamp;
DUMP(E,16)
---------------------------------------------
Typ=182 Len=5: 80,0,0,5,46
Typ=182 Len=5: 80,0,0,5,46
Typ=182 Len=5: 80,0,0,5,46
年：0X80000005-0X80000000=5
月：0x46-60=10
INTERVAL DAY TO SECOND数据类型
该数据类型常用来表示一个时间段，而非其他日期格式表示一个准确的时间，
如： 与sysdate做算法时是以天为单位，如需缩小范围需要/24 /60 /60等， 而是用interval则可直接指定单位并自动换算。
Oracle语法:
INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE } [ ( leading_precision ) ]
| SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.
范围值:
HOUR: 0 to 23
MINUTE: 0 to 59
SECOND: 0 to 59.999999999
eg:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
表示: 4天5小时12分10.222秒
INTERVAL '4 5:12' DAY TO MINUTE
表示: 4天5小时12分
INTERVAL '400 5' DAY(3) TO HOUR
表示: 400天5小时, 400为3为精度,所以"DAY(3)", 注意默认值为2.
INTERVAL '400' DAY(3)
表示: 400天
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
表示: 11小时12分10.2222222秒
INTERVAL '11:20' HOUR TO MINUTE
表示: 11小时20分
INTERVAL '10' HOUR
表示: 10小时
INTERVAL '10:22' MINUTE TO SECOND
表示: 10分22秒
INTERVAL '10' MINUTE
表示: 10分
INTERVAL '4' DAY
表示: 4天
INTERVAL '25' HOUR
表示: 25小时
INTERVAL '40' MINUTE
表示: 40分
INTERVAL '120' HOUR(3)
表示: 120小时
INTERVAL '30.12345' SECOND(2,4)
表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入.
INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND
表示: 20天 - 240小时 = 10天0秒
==================
该部分来源：http://www.oraclefans.cn/forum/showblog.jsp?rootid=140
INTERVAL DAY TO SECOND类型存储两个TIMESTAMP之间的时间差异，用日期、小时、分钟、秒钟形式表示。该数据类型的内部代码是183，长度位11字节：
l 4个字节表示天数（增加0X80000000偏移量）
l 小时、分钟、秒钟各用一个字节表示（增加60偏移量）
l 4个字节表示秒钟的小时差异（增加0X80000000偏移量）
以下是一个例子：
SQL> alter table testTimeStamp add f interval day to second ;
表已更改。
SQL> update testTimeStamp set f=(select interval '5' day + interval '10' second from dual);
已更新3行。
SQL> commit;
提交完成。
SQL> select dump(f,16) from testTimeStamp;
DUMP(F,16)
--------------------------------------------------------------------------------
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
日期：0X80000005-0X80000000=5
小时：60－60＝0
分钟：60－60＝0
秒钟：70－60＝10
秒钟小数部分：0X80000000-0X80000000=0
Reference:
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_expression001.htm#BABFDAEI;
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm; 官方文档；
http://www.cnblogs.com/xiaobaihome/archive/2012/03/14/2395568.html ； 日期问题大全
http://www.cnblogs.com/chuncn/archive/2009/04/29/1381282.html ； 日期函数大全;
2.ORACLE 日期时间数据类型与时间间隔数据类型讲解
23.1 概述
数据类型
说明
DATE
日期时间型，精确到秒
TIMESTAMP(fractional_seconds_precision)
时间精确到秒后面fractional_seconds_precision位，默认6位，最多9位
TIMESTAMP(fractional_seconds_precision)
WITH TIME ZONE
带时区信息，时间精确到秒后面fractional_seconds_precision位，默认6位，最多9位
TIMESTAMP(fractional_seconds_precision)
WITH LOCAL TIME ZONE
带时区信息，时间精确到秒后面fractional_seconds_precision位，默认6位，最多9位，但是你查询出来的是经过转换成会话所在时区的时间，是个神奇的字段哦
INTERVAL YEAR(year_precision) TO MONTH
存储一个时间间隔，year_precision说明年的整数位数，最多9位，默认2位。
INTERVAL DAY (day_precision) TO SECOND(fractional_seconds_precision)
存储一个时间间隔，day_precision说明天的整数位数，最多9位，默认2位。fractional_seconds_precision说明秒后面小数位数，默认6位，最多9位。
一般将前四种数据类型DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE 一般称之为日期时间数据类型(datetimes)，而将INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND 这两种数据类型称之为时间间隔数据类型(intervals).
23.2 小技巧：不用TO_DATE来输入或比较不带时分秒的日期
对于不带时分秒的日期，可用使用DATE这种方法，使用这种方法是后面的日期需要按照YYYY-MM-DD的格式，如下
insert into test_ly(a) values(DATE '2008-08-10')
如果是输入当天的0时0分0秒，使用
insert into test_ly(mydate) values(trunc(sysdate))
DATE方法还可以用于比较日期
select * from BOM_CALENDAR_DATES where calendar_code='HELS_CAL' AND ALENDAR_DATE>=DATE '2009-01-01' ORDER BY CALENDAR_DATE
如何查询某天的数据？
select * from test_ly where trunc(mydate)=date '2009-11-03'
对于格式是参数NLS_DATE_FORMAT的字符串(具体使用的哪种可以通过 select * from nls_session_parameters where parameter='NLS_DATE_FORMAT'; 查看，只是可惜的是这个参数一般没有修改成为符合国人习惯的YYYY-MM-DD格式)，还可以直接使用单引号将日期包括起来代表日期，比如：
insert into test_ly(mydate) values('01-1月-09')
比如我使用alter session set NLS_DATE_FORMAT='YYYY-MM-DD' 修改后
然后就这样可以插入日期了
insert into test_ly(mydate) values('2009-01-01')
23.3 某天星期几？
今天星期几？
select to_char(sysdate,'DAY', 'NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;
某天星期几？
select to_char(DATE '2009-11-07','DAY', 'NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;
select to_char(HIREDATE,'DAY','NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') as xingqi,HIREDATE from EMP;
如果当前会话的NLS_DATE_LANGUAGE参数(这个参数值可以通过动态视图nls_session_parameters查询到 )为SIMPLIFIED CHINESE，也可简单点：
select to_char(DATE '2009-11-07','DAY') from dual;
23.4 获取年，月，日，时，分，秒
23.4.1 获取年、月、日
对于4种日期时间型数据，获取年月日的方法都是一样的，使用EXTRACT函数。
select mydate,EXTRACT(YEAR FROM mydate) as date_nian,EXTRACT(MONTH FROM mydate) AS date_yue,EXTRACT(DAY FROM mydate) AS date_ri,
mytimestamp,EXTRACT(YEAR FROM mytimestamp) as ts_nian,EXTRACT(MONTH FROM mytimestamp) AS ts_yue,EXTRACT(DAY FROM mytimestamp) AS tm_ri FROM test_ly;
23.4.2 TIMESTAMP类型字段获取时分秒
获取的是记录本身的时分秒，与用户会话所处时区没有关系，注意，小时返回的是24小时制时间，秒钟会返回指定精度，默认是6位小数。
select mytimestamp,EXTRACT(HOUR FROM mytimestamp) as xiaoshi,EXTRACT(MINUTE FROM mytimestamp) as fenzhong,EXTRACT(SECOND FROM mytimestamp) as miao FROM test_ly;
23.4.3 TIMESTAMP WITH TIME ZONE类型字段获取时分秒
获取的是这个字段所记载的时区的分秒，但是获取的小时数却是时间标准时间(UTC)的小时 数，与用户会话所处时区没有关系。注意，小时返回的是24小时制时间，秒钟会返回指定精度，默认是6位小数。解释：当时记载的是东8区(北京时间所在时 区)的下午1点，但是取得的小时却是5，也就是13-8=5，换算成了世界标准时间。
select mytswtm,EXTRACT(HOUR FROM mytswtm) as xiaoshi,EXTRACT(MINUTE FROM mytswtm) as fenzhong,EXTRACT(SECOND FROM mytswtm) as miao FROM test_ly;
23.4.4 TIMESTAMP WITH LOCAL TIME ZONE类型字段获取时分秒
这种数据类型从某种程度上是欺骗你的，也是自适应的，它显示的字段值以及获取的时分秒与会话 所在的时区有关，会自动转化成会话所在时区的时间。你看，我当时插入的记录是东8区下午1点多，我用命令将自己会话的时区设置为东9区(ALTER SESSION SET TIME_ZONE = '+9:00';)，然后查看数据，会发现小时数加了一个小时。
select mytswltm,EXTRACT(HOUR FROM mytswltm) as xiaoshi,EXTRACT(MINUTE FROM mytswltm) as fenzhong,EXTRACT(SECOND FROM mytswltm) as miao FROM test_ly;
23.4.5 DATE类型数据获取时分秒
系统看起来并没有直接提供获取DATE数据类型时分秒的方面，前面用到的EXTRACT函数 对DATE数据类型来获取时分秒并不使用，看来只有另想办法。我想到的办法是将DATE类型转换为TIMESTAMP数据类型再取时分秒，事实证明可行， 或许还有更好的办法我不知道吧。
select mydate,EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) as xiaoshi,EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) as fenzhong,EXTRACT(SECOND FROM TO_TIMESTAMP(TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) as miao FROM test_ly;
23.5日期和TIMESTAMP类型之间的转换
将当前日期时间转换为TIMESTAMP使用SYSTIMESTAMP函数
SELECT SYSTIMESTAMP FROM dual;
将TIMESTAMP数据类型转换为某个时区的时间可以使用FROM_TZ函数
比如将2003-11-20 19:30:00这个时间转换为东8区时间：
SELECT FROM_TZ(TIMESTAMP '2003-11-20 19:30:00', '+8:00') FROM dual;
23.6查看、修改时区
23.6.1 当前会话所处时区
select SESSIONTIMEZONE from dual
23.6.2 数据库所处时区
SELECT DBTIMEZONE from dual
23.6.3更改当前会话所处时区
比如更改当前会话所处时区为东9区
ALTER SESSION SET TIME_ZONE = '+9:00';
23.6.4 如何查看TIMESTAMP WITH TIME ZONE数据类型存储的是那个时区？
还是使用EXTRACT函数，可以看出，分别使用的是东8区和东9区。
select mytswtm,extract(TIMEZONE_HOUR FROM mytswtm) AS shiqu from test_ly
23.6.5 如何查看总共有哪些时区地？
SELECT * FROM V$TIMEZONE_NAMES
23.7 DATE 和TIMESTAMP类型的区别
首先一个区别便是，DATE数据类型精确到秒，而其他三种(TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE)最多可以精确到秒后面的第9位小数，其他三种是DATE类型的扩展。
做试验说明更多：
create table test_ly(
mydate date,
mytimestamp TIMESTAMP(6),
mytswtm TIMESTAMP(6) WITH TIME ZONE,
mytswltm TIMESTAMP(6) WITH LOCAL TIME ZONE,
myiytm INTERVAL YEAR(2) TO MONTH,
myidts INTERVAL DAY(6) TO SECOND(6)
)
插入一行记录比较，注意LOCALTIMESTAMP返回的是会话当前时区的当前时间的 TIMESTAMP类型的值，不带时区信息，而CURRENT_TIMESTAMP返回的是会话当前时区的当前时间并带有当前会话时区的 TIMESTAMP WITH TIME ZONE类型的值。具体区别如下表：
Table 10-1. Comparison of functions that return current date and time
Function
Time zone
Datatype returned
CURRENT_DATE
Session
DATE
CURRENT_TIMESTAMP
Session
TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP
Session
TIMESTAMP
SYSDATE
Server
DATE
SYSTIMESTAMP
Server
TIMESTAMP WITH TIME ZONE
insert into test_ly(mydate,mytimestamp,mytswtm,mytswltm)
values(sysdate,LOCALTIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
要将LOCALTIMESTAMP转换成CURRENT_TIMESTAMP可以使用
select TO_TIMESTAMP_TZ(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM') from dual;
然后我看看插入的几条记录，显示的时间内容基本一致。
我将我当前会话的时区改成东9区，然后再插入记录
insert into test_ly(mydate,mytimestamp,mytswtm,mytswltm)
values(sysdate,LOCALTIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
这时候返回记录看看异同：发现sysdate记载的是服务器时间，与会话所取时区没有关 系，TIMESTAMP类型字段显示的是插入记录时会话时区的时间，不带时区，你看东8区是上午10点多，东9区则是上午11点多，TIMESTAMP WITH TIME ZONE类型字段显示的是插入记录时会话时区的时间，带时区信息，也就是东9区什么时候，TIMESTAMP WITH LOCAL TIME ZONE字段显示时没有时区信息，这是根据会话所在时区灵活显示时间的。
为了更好的说明TIMESTAMP WITH LOCAL TIME ZONE类型字段是根据会话灵活显示时间的，我现在又将时区修改为东8区，再看显示，你会发现该字段显示的小时数变了，和sysdate相同了，这就是神 奇之处，显示给你看的是根据会话所在时区转换后的时间，而TIMESTAMP和TIMESTAMP WITH TIME ZONE字段则不会转化显示时间给你看。
如何插入指定日期的TIMESTAMP数据？
insert into test_ly(mydate,mytimestamp,mytswtm,mytswltm)
values(sysdate,TIMESTAMP '1983-11-09 04:26:50.124',TIMESTAMP '1983-11-09 04:26:50.124 +8:00',TIMESTAMP '1983-11-09 04:26:50.124 +8:00')
23.8如何对date类型字段实现各种细度的汇总？
我想这些东东可能就是oracle宣称的商务只能可以做到很小的细微粒度吧，比如查看某些日子有多少人登陆过EBS系统：
23.8.1 按日汇总
select TRUNC(start_time) AS RIQI,count(*) as denglurenshu from FND_LOGINS where start_time> DATE '2009-10-01' group by TRUNC(start_time)
23.8.2 按月汇总
select TRUNC(start_time,'MONTH') AS YUEFEN,count(*) as denglurenshu from FND_LOGINS where start_time> DATE '2009-09-01' group by TRUNC(start_time,'MONTH')
23.8.3 按年汇总
select TRUNC(start_time,'YEAR') AS nian,count(*) as denglurenshu from FND_LOGINS group by TRUNC(start_time,'YEAR')
23.8.4 按季度汇总
select TRUNC(start_time,'Q') AS jidu,count(*) as denglurenshu from FND_LOGINS where start_time> DATE '2009-01-01' group by TRUNC(start_time,'Q')
23.8.5 按周汇总
不过值得注意的是，默认周日是一周的开始，周六是一周的结束哦。当然可以通过更改数据库参数NLS_TERRITORY来实现更改。
select TRUNC(start_time,'DAY') AS jidu,count(*) as denglurenshu from FND_LOGINS where start_time> DATE '2009-10-01' group by TRUNC(start_time,'DAY')
23.8.6按小时汇总
select TRUNC(start_time,'HH') AS jidu,count(*) as denglurenshu from FND_LOGINS where start_time> DATE '2009-11-01' group by TRUNC(start_time,'HH')
23.8.7 按分钟汇总
select TRUNC(start_time,'MI') AS jidu,count(*) as denglurenshu from FND_LOGINS where start_time> DATE '2009-11-03' group by TRUNC(start_time,'MI')
23.9日期时间类型数据相加减，相差多少?
23.9.1 多少天(时分秒)之前或者多少天(时分秒)之后
直接用DATE型数据与数字(可以使用小数)进行相加减即可，比如
SELECT SYSDATE + 2 FROM DUAL
SELECT SYSDATE - 2.5 FROM DUAL
比如计算3天5小时后是什么时候可以这么计算：
SELECT SYSDATE + (3 + 5/24) FROM DUAL
23.9.2 多少(年)月之前或者多少(年)月之后
使用函数ADD_MONTHS进行，比如
select ADD_MONTHS(SYSDATE,1) FROM DUAL
值得注意的是，月有大小，比如2009年10月有31日，那么加一个月后是11月31日吗？肯定不是啦，11月没有31日，那么应该是11月30日。
select ADD_MONTHS(DATE '2009-10-31',1) FROM DUAL
同样的，如果是闰年闰月之类的问题呢，比如2008年2月有29天，那么这天一年后是哪天呢？当然是2009年2月28日。
select ADD_MONTHS(DATE '2008-02-29',12) FROM DUAL
23.9.3 使用INTERVAL来计算多少年多少月多少小时多少分钟之前之后
随便举几个例子，interval的用法可以参考sql reference文档。
10年2个月以后的日期
select sysdate + interval '10-2' year(2) to month from dual
20年以后
select sysdate + interval '20' year from dual
5个月后
select sysdate + interval '5' month from dual
5天10分钟以后
select sysdate + interval '5 00:10' day to minute from dual
5小时10分钟秒以后
select sysdate + interval '5:10:10.11111' hour to second(5) from dual
10年5小时10分钟后
select sysdate + interval '10' year + interval '5:10' hour to minute from dual
23.9.4 使用NUMTODSINTERVAL和NUMTOYMINTERVAL 来计算多少年(月)，时(分)之后
用法：NUMTODSINTERVAL(n,[‘DAY’|’HOUR’|’MINUTE’|’SECOND’])
NUMTOYNINTERVAL(n,[‘YEAR’|’MONTH’])
用法示例：
一个小时后：
select sysdate,sysdate + NUMTODSINTERVAL(1,'HOUR') from dual
5个月5小时后：
select sysdate,sysdate + NUMTOYMINTERVAL(5,'MONTH') + NUMTODSINTERVAL(5,'HOUR') from dual
23.9.5 本月，今年还剩多少天？
基本用法是使用LAST_DAY函数，但是还有些问题要注意。
本月还剩多少天？
请注意，LAST_DAY函数返回的是参数日期月份的最后一天的同小时同分钟同秒钟的时间，其实本月的最后一时是下月的第一天的0时0分0秒。
select LAST_DAY(trunc(sysdate)) + 1 - sysdate from dual
今年还剩多少天？
select ADD_MONTHS(TRUNC(sysdate,'YEAR'),12) - sysdate from dual
23.9.6如何计算两个timestamp相差多少年月，多少天多少小时多少分钟多少秒
select (SYSTIMESTAMP - TIMESTAMP '1983-11-09 04:26:50.124 +8:00') YEAR TO MONTH as xiangchaninayue from dual
select (SYSTIMESTAMP - TIMESTAMP '1983-11-09 04:26:50.124 +8:00') DAY(6) TO SECOND as xiangchatianshifenmiao from dual
23.10 如何让datetimes字段类型显示更加符合国人习惯？
可以更改会话让当前的日期更加符合国人的习惯，最好是更改数据库设置，
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
还可以对日期进行格式化，比如
select TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual
但是这种格式化对TIMESTAMP类型数据不管用，还是显示比较丑陋的方式，需要进行如下的格式化才好：
select to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SSxFF TZH:TZM') from dual
select to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SSxFF') from dual
select to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') from dual3.
ORACLE中"周"相关知识描述
1.1 日期格式化函数
TO_CHAR(X [,FORMAT])：将X按FORMAT格式转换成字符串。X是一个日期，FORMAT是一个规定了X采用何种格式转换的格式字符串，FORMAT与周相关的有W，WW，IW，D，FMWW。
W 的含义是一个月的第几周。是按照ORACLE自定义的标准周来返回周数。
IW是ISO标准周，它的含义是ISO标准周以周别为“主线”，每年最多可以有53个周别，但是每年至少要包含52个周别；如果一年当中第52周别之后至当年的12月31日之间，还有大于或等于4天的话，则定为当年的第53周，否则剩余这些天数被归为下一年的第1周；如果在不足52周别的话，则以下一年的时间来补；每周固定的从周一开始作为本周的第1天，到周日作为本周的第7天；比如：在Oracle中 2012年01月01号依然属于IW周别2011年的第52周的第7天。这个用到的比较多。
WW是ORACLE自定义的标准周，它的含义是每年的1月1日作为当年的第一周的第一天（不管当年的1月1日是星期几）；比如：2014年01年01是周三，在Oracle中被定义为2014年WW的第一周的第一天。一般很少用到。
D是返回当前日期是这个星期的第几天。是按照从周日到周六来进行计算的，这是要注意的地方。
FMWW该年1月1号（不考虑属星期几）开始至该年该一个星期日为第一周，第二周是从该年第一个星期开始算（这与IW算法相同）一年的最后一周以该年的12月31号做为截止。
1.2 日期时间运算函数
NEXT_DAY(X,Y)：用于计算X时间后第一个星期Y的时间。Y是一个字符串，表示用当前会话语言表示的一周中某一天的全称（如星期一、星期二等），也可以是数值。
TRUNC(X [,FORMAT])：截断日期, FORMAT 中与周相关的有D，IW，WW，W，FMWW。
W 的含义是一个月的第几周。是按照ORACLE自定义的标准周来返回的是一个周数。
IW是ISO标准周，返回是的当前日期所在周的周一。
WW是ORACLE自定义的标准周。返回ORACLE自定义标准周所在的周一。
D 的含义是的返回当前星期的第一天。令人感到奇怪的是根据D返回的是当前星期的第一天是星期日。这点是我们要注意的地方。
FMWW该年1月1号（不考虑属星期几）开始至该年该一个星期日为第一周，第二周是从该年第一个星期开始算（这与IW算法相同）一年的最后一周以该年的12月31号做为截止。取周的开始时间时在跨年的时候与IW有些区别，比如2012年01年01使用FMWW的时候，周的开始时间是2012-01-01，使用IW时，周的开始时间是2011/12/26。
ROUND(X [,FORMAT])：日期的四舍五入FORMAT中与周相关的有DAY。按周一到周三和周四到周日四舍五入到最近的周日。
2 根据给定时间取一周的开始时间和结束时间
--取周的开始时间和结束时间SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') AS STARTDATE FROM DUAL;--本周周一SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') + 6 AS ENDDATE FROM DUAL;--本周周日SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') - 7 AS STARTDATE FROM DUAL;---上周周一 SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') - 1 AS ENDDATE FROM DUAL;--上周周日
3 根据给定周数取一周的开始时间和结束时间
取自然周的开始时间和结束时间的难点就需要判断年初的那几天是属于本年的第一周，还是属于上一年的最后一周，根据IW自然周的定义，少于等于3天是本年的话，属于上一年的最后一周，大于等于4天属于本年的话，加上上年的最后几天，算成本年的第一周。
--按照周一到周日为一周算周的开始时间和结束时间（IW）自然周WITH PARAMS AS (SELECT TRUNC(TO_DATE('2009-01-01','YYYY-MM-DD'),'YYYY') AS SD FROM DUAL)SELECT LEVEL 周次,DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) 当周第一天,DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) + 6当周最后一天FROM DUAL DLEFT JOIN PARAMS PM ON 1=1CONNECT BY LEVEL<=53--按照周日到周六为一周算周的开始时间和结束时间（D）SELECT LEVEL 周次,(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY')-7) + (7-TO_CHAR(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY'),'D')+1)+(LEVEL-1)*7 当周第一天, (TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY')-7) + (7-TO_CHAR(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY'),'D')+1)+(LEVEL-1)*7+6 当周最后一天FROM DUAL CONNECT BY LEVEL<=53--按照ORACLE标准（WW）SELECT LEVEL 周次,TO_DATE('2013-01-01','YYYY-MM-DD')+(LEVEL-1)*7 当周第一天, TO_DATE('2013-01-01','YYYY-MM-DD')+(LEVEL-1)*7+ DECODE((TO_CHAR(TO_DATE('2013-12-31','YYYY-MM-DD'),'DDD')-(LEVEL-1)*7),1,0,2,1,6) 当周最后一天FROM DUAL CONNECT BY LEVEL<=53
4 获取一年的最大周次
--获取一年中的最大周次（IW）中国日历自然周WITH PARAMS AS (SELECT '2014' AS NF FROM DUAL)SELECT TO_CHAR(TO_DATE(PM.NF '-12-28','YYYY-MM-DD'),'IYYYIW') FROM DUAL LEFT JOIN PARAMS PM ON 1=1
上述SQL通过卡每年的12月28号属于哪个周，也判断一年有多少个自然周。
5 特别应该注意的地方
取周别的时候最好把年份带上，第一因为周别是相对于哪年的第几周，第二因为当使用IW的时候对于一年的开始那几天和结束那几天可能会产生一样的周数，就分不清属于哪年的第一周。
- Oracle的时间总结 -- oracle知识2016/09/30
- oracle开发必学的知识----oracle 时间函数
- Oracle触发器知识总结
- Oracle 小知识 总结
- Oracle 小知识 总结
- Oracle知识总结
- oracle知识总结
- Oracle小知识总结
- oracle入门知识总结
- oracle知识总结
- Oracle常用知识总结
- Oracle小知识总结
- oracle小知识总结
- Oracle 小知识 总结
- oracle知识总结
- Oracle常用知识的一些总结
- oracle存储过程的知识总结
- 史上最全的oracle常用知识总结
- java中组合与继承的区别
- mha安装部署
- 欢迎使用CSDN-markdown编辑器
- Solr建立索引时，过滤HTML标签
- 文字溢出容器问题
- Oracle的时间总结 -- oracle知识2016/09/30
- 20160928_Oracle数据库管理记录
- uva 101
- 一些适合初级安卓开发者的项目
- iOS 内存管理
- 【HNOI 2010】Bounce 弹飞绵羊 分块
- jtessboxeditor 中文乱码
- 【hdu 2196】Computer（树形dp）
- （转载）性能调优--永远超乎想象 （原文最终修订于 2006-08-28 晚上11：48：38）
