Oracle关于时间/日期的操作

Oracle关于时间/日期的操作

　　1.日期时间间隔操作

　　当前时间减去7分钟的时间

　　Java代码
select sysdate,sysdate - interval '7' MINUTE from dual 

select sysdate,sysdate - interval '7' MINUTE from dual

　　当前时间减去7小时的时间

　　Java代码
select sysdate - interval '7' hour from dual 

select sysdate - interval '7' hour from dual

　　当前时间减去7天的时间

　　Java代码
select sysdate - interval '7' day from dual 

select sysdate - interval '7' day from dual

　　当前时间减去7月的时间

　　Java代码
select sysdate,sysdate - interval '7' month from dual 

select sysdate,sysdate - interval '7' month from dual

　　当前时间减去7年的时间

　　Java代码
select sysdate,sysdate - interval '7' year from dual 

select sysdate,sysdate - interval '7' year from dual

　　时间间隔乘以一个数字

　　Java代码
select sysdate,sysdate - 8 *interval '2' hour from dual 

select sysdate,sysdate - 8 *interval '2' hour from dual

　　2.日期到字符操作

Java代码
　　select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual  
 
　　select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual  
 
　　select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual  
 
　　select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual 

　　select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

　　select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

　　select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual

　　select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
　　参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)

　　3. 字符到日期操作

Java代码
　　select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual 

　　select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

　　具体用法和上面的to_char差不多。

　　4. trunk/ ROUND函数的使用

Java代码
　　select trunc(sysdate ,'YEAR') from dual  
 
　　select trunc(sysdate ) from dual  
 
　　select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual 

　　select trunc(sysdate ,'YEAR') from dual

　　select trunc(sysdate ) from dual

　　select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual

　　5.oracle有毫秒级的数据类型

　　--返回当前时间 年月日小时分秒毫秒

Java代码
　　select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual; 

　　select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;

　　--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)

　　Java代码
select to_char(current_timestamp(9),'MI:SSxFF') from dual; 

select to_char(current_timestamp(9),'MI:SSxFF') from dual;

　　6.计算程序运行的时间(ms)

Java代码
　　declare  
 
　　type rc is ref cursor;  
 
　　l_rc rc;  
 
　　l_dummy all_objects.object_name%type;  
 
　　l_start number default dbms_utility.get_time;  
 
　　begin  
 
　　for I in 1 .. 1000 
 
　　loop  
 
　　open l_rc for 
 
　　'select object_name from all_objects '||  
 
　　'where object_id = ' || i;  
 
　　fetch l_rc into l_dummy;  
 
　　close l_rc;  
 
　　end loop;  
 
　　dbms_output.put_line  
 
　　( round( (dbms_utility.get_time-l_start)/100, 2 ) ||  
 
　　' seconds...' );  
 
　　end; 

　　declare

　　type rc is ref cursor;

　　l_rc rc;

　　l_dummy all_objects.object_name%type;

　　l_start number default dbms_utility.get_time;

　　begin

　　for I in 1 .. 1000

　　loop

　　open l_rc for

　　'select object_name from all_objects '||

　　'where object_id = ' || i;

　　fetch l_rc into l_dummy;

　　close l_rc;

　　end loop;

　　dbms_output.put_line

　　( round( (dbms_utility.get_time-l_start)/100, 2 ) ||

　　' seconds...' );

　　end;

    7、add_months()用于从一个日期值增加或减少一些月份





1〉获得若干分钟前的时间 Java代码
select sysdate,sysdate - N/(60*24) from dual; 

select sysdate,sysdate - N/(60*24) from dual;
或者 Java代码
select sysdate,sysdate - interval 'N' minute from dual 

select sysdate,sysdate - interval 'N' minute from dual

2〉获得若干分钟后的时间 sJava代码
elect sysdate,sysdate + N/(60*24) from dual; 

elect sysdate,sysdate + N/(60*24) from dual;
或者 Java代码
select sysdate,sysdate + interval 'N' minute from dual 

select sysdate,sysdate + interval 'N' minute from dual

注释："N"表示需要减去或者加上的分钟数

3〉获得若干秒前的时间 sJava代码
elect sysdate,sysdate - N*0.00001 from dual; 

elect sysdate,sysdate - N*0.00001 from dual;
或者 Java代码
select sysdate,sysdate - N/(60*60*24) from dual; 

select sysdate,sysdate - N/(60*60*24) from dual;
或者 Java代码
select sysdate,sysdate - interval 'N' second from dual  

select sysdate,sysdate - interval 'N' second from dual

4〉获得若干秒后的时间 Java代码
select sysdate,sysdate + N*0.00001 from dual; 

select sysdate,sysdate + N*0.00001 from dual;
或者 Java代码
select sysdate,sysdate - N/(60*60*24) from dual; 

select sysdate,sysdate - N/(60*60*24) from dual;
或者 Java代码
select sysdate,sysdate + interval 'N' second from dual 

select sysdate,sysdate + interval 'N' second from dual

注释："N"表示需要减去或者加上的秒数
注释：N*0.00001 等价于 N/(60*60*24)

5〉获得若干小时前的时间 Java代码
select sysdate,sysdate - N*/24 from dual; 

select sysdate,sysdate - N*/24 from dual;
或者 Java代码
select sysdate,sysdate - interval 'N' hour from dual 

select sysdate,sysdate - interval 'N' hour from dual

6〉获得若干小时后的时间Java代码
select sysdate,sysdate + N*/24 from dual; 

select sysdate,sysdate + N*/24 from dual;
或者 Java代码
select sysdate,sysdate + interval 'N' hour from dual 

select sysdate,sysdate + interval 'N' hour from dual

注释："N"表示需要减去或者加上的小时数

7〉获得若干月之后同一时间 Java代码
select sysdate,add_months(sysdate,N) from dual; 

select sysdate,add_months(sysdate,N) from dual;
或者 Java代码
select sysdate,sysdate + interval 'N' month from dual 

select sysdate,sysdate + interval 'N' month from dual

8〉获得若干月之前的同一时间 Java代码
select sysdate,add_months(sysdate,-N) from dual; 

select sysdate,add_months(sysdate,-N) from dual;
或者 Java代码
select sysdate,sysdate - interval 'N' month from dual 

select sysdate,sysdate - interval 'N' month from dual

注释："N"表示需要减去或者加上的月数

9〉获得某个日期所在月份的第一天 Java代码
select sysdate,trunc(sysdate,'mm') from dual; 

select sysdate,trunc(sysdate,'mm') from dual;

10〉获得某个日期所在年的第一天 Java代码
select sysdate,trunc(sysdate,'yyyy') from dual; 

select sysdate,trunc(sysdate,'yyyy') from dual;
或者 Java代码
select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy')) * 100 + 1) * 100 + 1),'yyyymmdd') from dual 

select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy')) * 100 + 1) * 100 + 1),'yyyymmdd') from dual

11〉获得某个日期所在年的最后一天 Java代码
select sysdate,to_date((to_char(sysdate,'yyyy') || '1231'),'yyyymmdd') from dual; 

select sysdate,to_date((to_char(sysdate,'yyyy') || '1231'),'yyyymmdd') from dual;
或者 Java代码
select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy'))* 100 + 12) * 100 + 31),'yyyymmdd') from dual 

select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy'))* 100 + 12) * 100 + 31),'yyyymmdd') from dual

12〉获得某个日期所在月份的最后一天 Java代码
select sysdate,last_day(sysdate) from dual; 

select sysdate,last_day(sysdate) from dual;

13〉获得两个日期之间的时数 Java代码
select t.intime,t.outtime,trunc(24*(t.outtime-t.intime)) from hr_carding t 

select t.intime,t.outtime,trunc(24*(t.outtime-t.intime)) from hr_carding t

14〉获得两个日期之间的天数 Java代码
select t.begintime,t.endtime,trunc(t.endtime-t.begintime) from hr_absence t 

select t.begintime,t.endtime,trunc(t.endtime-t.begintime) from hr_absence t

15〉获得若干天前的时间 sJava代码
elect sysdate,sysdate - N from dual; 

elect sysdate,sysdate - N from dual;
或者 Java代码
select sysdate,sysdate - interval 'N' day from dual 

select sysdate,sysdate - interval 'N' day from dual

16〉获得若干天后的时间 Java代码
select sysdate,sysdate + N from dual; 

select sysdate,sysdate + N from dual;
或者 Java代码
select sysdate,sysdate + interval 'N' day from dual 

select sysdate,sysdate + interval 'N' day from dual

注释："N"表示需要减去或者加上的天数

17〉获得若干年前的时间 Java代码
select sysdate,add_months(sysdate,-12*N) from dual; 

select sysdate,add_months(sysdate,-12*N) from dual;
或者 Java代码
select sysdate,sysdate - interval 'N' year from dual 

select sysdate,sysdate - interval 'N' year from dual

16〉获得若干年后的时间 Java代码
select sysdate,add_months(sysdate,12*N) from dual; 

select sysdate,add_months(sysdate,12*N) from dual;
或者 Java代码
select sysdate,sysdate + interval 'N' year from dual 

select sysdate,sysdate + interval 'N' year from dual

注释："N"表示需要减去或者加上的年数

17〉相隔若干小时的时间 Java代码
select sysdate,sysdate - M * interval 'N' hour from dual  

select sysdate,sysdate - M * interval 'N' hour from dual

18〉检索出某一个日期所在月份的所有天信息

Java代码
select trunc(to_date('X','yyyymmdd'),'mm') from dual  
union all  
select trunc(to_date('X','yyyymmdd'),'mm') + rownum from dual  
connect by rownum <= (last_day(to_date('X','yyyymmdd')) - trunc(to_date('X','yyyymmdd'),'mm')) 

select trunc(to_date('X','yyyymmdd'),'mm') from dual
union all
select trunc(to_date('X','yyyymmdd'),'mm') + rownum from dual
connect by rownum <= (last_day(to_date('X','yyyymmdd')) - trunc(to_date('X','yyyymmdd'),'mm'))

或者
Java代码
select first + rownum - 1 myday  
from (select trunc(to_date('X','yyyymmdd'),'MM') first,trunc(last_day(to_date('X','yyyymmdd'))) last from dual)  
connect by rownum <= last - first + 1 

select first + rownum - 1 myday
from (select trunc(to_date('X','yyyymmdd'),'MM') first,trunc(last_day(to_date('X','yyyymmdd'))) last from dual)
connect by rownum <= last - first + 1

其中：字符串'X'表示某一个日期

19〉怎样知道今天是星期几

Java代码
select to_char(sysdate,'day') from dual;  

select to_char(sysdate,'day') from dual;

在获取之前可以设置日期语言,如:
Java代码
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
还可以在函数中指定
Java代码
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = AMERICAN') from dual;  

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = AMERICAN') from dual;
其它更多用法,可以参考to_char与to_date函数

20〉本月的天数
Java代码
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual 

SELECT to_char(last_day(SYSDATE),'dd') days FROM dual

21〉今年的天数
Java代码
select add_months(trunc(sysdate, 'year'), 12) - trunc(sysdate, 'year') /*下一年的第一天减去今年的第一天*/ 
from dual 

select add_months(trunc(sysdate, 'year'), 12) - trunc(sysdate, 'year') /*下一年的第一天减去今年的第一天*/
from dual

22〉下个星期一的日期

Java代码
SELECT Next_day(SYSDATE,'monday') FROM dual 

SELECT Next_day(SYSDATE,'monday') FROM dual

23〉怎么样从数据库中获得毫秒
9i以上版本，有一个timestamp类型获得毫秒，如
Java代码
select to_char(systimestamp ,'yyyy-mm-dd hh24:mi:ssxff') time1,   
to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ssxff') time2   
from dual; 

select to_char(systimestamp ,'yyyy-mm-dd hh24:mi:ssxff') time1,
to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ssxff') time2
from dual;

24〉检索某一年度的日历表：

Java代码
select case 
when (new_yweek = min(new_yweek)  
over(partition by mon order by new_yweek)) then  
mon  
else 
null 
end as mon,  
new_yweek as yweek,  
row_number() over(partition by mon order by new_yweek) as mweek,  
sum(decode(wday, '1', mday, null)) as sunday,  
sum(decode(wday, '2', mday, null)) as monday,  
sum(decode(wday, '3', mday, null)) as tuesday,  
sum(decode(wday, '4', mday, null)) as wednesday,  
sum(decode(wday, '5', mday, null)) as thursday,  
sum(decode(wday, '6', mday, null)) as friday,  
sum(decode(wday, '7', mday, null)) as saturday,  
&year as year  
from (select to_date(&year || '0101', 'yyyymmdd') + rownum - 1 as everyday,  
to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'mm') as mon,  
to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'w') as mweek,  
to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'ww') as yweek,  
case 
when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and  
(to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'd') <  
to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then  
to_char(to_char(to_date(&year || '0101', 'yyyymmdd') +  
rownum - 1,  
'ww') + 1,  
'fm00')  
else 
to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'ww')  
end as new_yweek,  
to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'd') as wday,  
to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1,  
'dd') as mday  
from (select rownum r from dual connect by rownum <= 366)  
where rownum <=  
to_char(to_date(&year || '1231', 'yyyymmdd'), 'ddd'))  
group by mon, new_yweek