Oracle對日期時間操作

1.日期時間間隔操作

當前時間減去7分鐘的時間
select sysdate,sysdate - interval '7' MINUTE from dual

當前時間減去7小時的時間
select sysdate - interval '7' hour from dual

當前時間減去7天的時間
select sysdate - interval '7' day from dual

當前時間減去7月的時間
select sysdate,sysdate - interval '7' month from dual

當前時間減去7年的時間
select sysdate,sysdate - interval '7' year from dual

時間間隔乘以一個數字
select sysdate,sysdate - 8 *interval '2' hour from dual

2.日期到字元操作

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. 字元到日期操作

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

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

4. trunk/ ROUND函式的使用

select trunc(sysdate ,'YEAR') from dual

select trunc(sysdate ) from dual

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

5.oracle有毫秒級的資料型別

--返回當前時間 年月日小時分秒毫秒

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

--返回當前 時間的秒毫秒，可以指定秒後面的精度(最大=9)

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

6.計算程式執行的時間(ms)

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〉獲得若干分鐘前的時間
select sysdate,sysdate - N/(60*24) from dual;
或者
select sysdate,sysdate - interval 'N' minute from dual

2〉獲得若干分鐘後的時間 s
elect sysdate,sysdate + N/(60*24) from dual;
或者
select sysdate,sysdate + interval 'N' minute from dual

註釋："N"表示需要減去或者加上的分鐘數

3〉獲得若干秒前的時間 s
elect sysdate,sysdate - N*0.00001 from dual;
或者
select sysdate,sysdate - N/(60*60*24) from dual;
或者
select sysdate,sysdate - interval 'N' second from dual

4〉獲得若干秒後的時間
select sysdate,sysdate + N*0.00001 from dual;
或者
select sysdate,sysdate - N/(60*60*24) from dual;
或者
select sysdate,sysdate + interval 'N' second from dual

註釋："N"表示需要減去或者加上的秒數
註釋：N*0.00001 等價於 N/(60*60*24)

5〉獲得若干小時前的時間
select sysdate,sysdate - N*/24 from dual;
或者
select sysdate,sysdate - interval 'N' hour from dual

6〉獲得若干小時後的時間
 select sysdate,sysdate + N*/24 from dual;
或者
select sysdate,sysdate + interval 'N' hour from dual

註釋："N"表示需要減去或者加上的小時數

7〉獲得若干月之後同一時間
select sysdate,add_months(sysdate,N) from dual;
或者
select sysdate,sysdate + interval 'N' month from dual

8〉獲得若干月之前的同一時間
select sysdate,add_months(sysdate,-N) from dual;
或者
select sysdate,sysdate - interval 'N' month from dual

註釋："N"表示需要減去或者加上的月數

9〉獲得某個日期所在月份的第一天
select sysdate,trunc(sysdate,'mm') from dual;

10〉獲得某個日期所在年的第一天
select sysdate,trunc(sysdate,'yyyy') from dual;
或者
select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy')) * 100 + 1) * 100 + 1),'yyyymmdd') from dual

11〉獲得某個日期所在年的最後一天
select sysdate,to_date((to_char(sysdate,'yyyy') || '1231'),'yyyymmdd') from dual;
或者
select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy'))* 100 + 12) * 100 + 31),'yyyymmdd') from dual

12〉獲得某個日期所在月份的最後一天
select sysdate,last_day(sysdate) from dual;

13〉獲得兩個日期之間的時數
select t.intime,t.outtime,trunc(24*(t.outtime-t.intime)) from hr_carding t

14〉獲得兩個日期之間的天數
select t.begintime,t.endtime,trunc(t.endtime-t.begintime) from hr_absence t

15〉獲得若干天前的時間 s
elect sysdate,sysdate - N from dual;
或者
select sysdate,sysdate - interval 'N' day from dual

16〉獲得若干天后的時間
select sysdate,sysdate + N from dual;
或者
select sysdate,sysdate + interval 'N' day from dual

註釋："N"表示需要減去或者加上的天數

17〉獲得若干年前的時間
select sysdate,add_months(sysdate,-12*N) from dual;
或者
select sysdate,sysdate - interval 'N' year from dual

16〉獲得若干年後的時間
select sysdate,add_months(sysdate,12*N) from dual;
或者
select sysdate,sysdate + interval 'N' year from dual

註釋："N"表示需要減去或者加上的年數

17〉相隔若干小時的時間
select sysdate,sysdate - M * interval 'N' hour from dual

18〉檢索出某一個日期所在月份的所有天資訊

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 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〉怎樣知道今天是星期幾

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

在獲取之前可以設定日期語言,如:

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
還可以在函式中指定

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

20〉本月的天數

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

21〉今年的天數

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

22〉下個星期一的日期

SELECT Next_day(SYSDATE,'monday') FROM dual

23〉怎麼樣從資料庫中獲得毫秒
9i以上版本，有一個timestamp型別獲得毫秒，如

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〉檢索某一年度的日曆表：

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