Oracle 中時間的計算

Oracle中表示時間有DATE和TIMESTAMP,DATE可以儲存年,月,日,小時,分鐘,秒.

TIMESTAMP是DATE的擴充,可以儲存年,月,日,小時,分鐘,秒,同時還可以儲存秒的小數部分.秒的小數部分可以為9位即納秒,預設為6為的微秒.

表示時間差的為INTERVAL:INTERVAL YEAR TO MONTH 和INTERVAL DAY TO SECOND兩種.

 

1,Date類型:sysdate和current_date

1. 日期格式參數 含義說明

D 一周中的星期幾,數字

DAY 一周中的星期幾的名字，使用空格填充到 9 個字元

DD 月中的第幾天

DDD 年中的第幾天

DY 一周中的星期幾的簡寫名

IW ISO 標準的年中的第幾周

IYYY ISO 標準的四位年份

YYYY 四位年份

YYY,YY,Y 年份的最後三位，兩位，一位

HH 小時，按 12 小時計

HH24 小時，按 24 小時計

MI 分

SS 秒

MM 月

Mon 月份的簡寫

Month 月份的全名

W 該月的第幾個星期

WW 年中的第幾個星期

 

從日期到字串操作:to_char(日期,日期格式參數).

例:

select sysdate,to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual
select sysdate,to_char(sysdate,'DD/MM/YYYY HH:MI:SS') from dual

select sysdate,to_char(sysdate,'DD/MON/YYYY') from dual
select sysdate,to_char(sysdate,'DDD') from dual

 

從字串到日期轉換:to_date(字串,日期格式參數).日期格式參數的組合要比to_char少.

例:

select to_date('2010/07/16 21:15:37','yyyy/mm/dd hh24:mi:ss') from dual

select to_date('198','DDD') from dual

 

兩個日期相減得到的是一個以天為單位的number,帶小數.

天：
ROUND(TO_NUMBER(DATE1 - DATE2))
小時：
ROUND(TO_NUMBER(DATE1 - DATE2) * 24)
分鐘：
ROUND(TO_NUMBER(DATE1 - DATE2) * 24 * 60)
秒：
ROUND(TO_NUMBER(DATE1 - DATE2) * 24 * 60 * 60)
毫秒：
ROUND(TO_NUMBER(DATE1 - DATE2) * 24 * 60 * 60 * 1000)

 

日期和INTERVAL的操作:加減操作.

目前時間減去 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,TIMESTAMP類型:systimestamp和CURRENT_TIMESTAMP.

FF [1..9]小數秒.

從字串到時間戳記操作:to_timestamp(字串,日期格式參數).

select to_timestamp('2010/07/16 21:15:37','yyyy/mm/dd hh24:mi:ss') from dual
select to_timestamp('2010/07/16 21:15:37.123456','yyyy/mm/dd hh24:mi:ss.ff') from dual

select to_timestamp('2010/07/16 21:15:37.123456789','yyyy/mm/dd hh24:mi:ss.ff9') from dual

 

從時間戳記到字串操作:to_char(時間戳記,日期格式參數).

select to_char(systimestamp,'yyyy/mm/dd hh24:mi:ss') from dual

select to_char(systimestamp,'yyyy/mm/dd hh24:mi:ss.ff7') from dual

select to_char(systimestamp,'DDD') from dual

 

將date轉為timestamp,轉後的的timestamp的小數秒為0:

select systimestamp ,CAST (sysdate  AS timestamp)  from dual

 

將timestamp轉為date,轉後的date可能和前面的date有一秒之差:

select sysdate,CAST (systimestamp  AS DATE)  from dual

 

兩個timestamp相減的結果是interval 格式為 "天 小時 分 秒 微妙" DAY TO Second 格式.

 

本文用到的表:

CREATE TABLE timetest
(
  ID         INTEGER,
  BEGINTMT   TIMESTAMP(6),
  ENDTMT     TIMESTAMP(6),
  BEGINDATE  DATE,
  ENDDATE    DATE
)

insert into timetest values(1,to_timestamp('2010/05/12 12:23:34:4500','yyyy/mm/dd hh24:mi:ss:ff4'),
to_timestamp('2010/05/13 13:34:45:5600','yyyy/mm/dd hh24:mi:ss:ff4'),
to_date('2010/05/12 12:23:34','yyyy/mm/dd hh24:mi:ss'),
to_date('2010/05/13 13:34:45','yyyy/mm/dd hh24:mi:ss'))

 

insert into timetest values(2,to_timestamp('2009/04/12 12:33:35:4600','yyyy/mm/dd hh24:mi:ss:ff4'),
to_timestamp('2010/05/13 13:34:45:5600','yyyy/mm/dd hh24:mi:ss:ff4'),
to_date('2009/05/12 12:23:34','yyyy/mm/dd hh24:mi:ss'),
to_date('2010/05/12 12:23:34','yyyy/mm/dd hh24:mi:ss'))

 

資料為:

select ID,BEGINTMT, ENDTMT, ENDTMT- BEGINTMT from timetest

ID    BEGINTMT                    ENDTMT                        ENDTMT-BEGINTMT
1    12/05/2010 12:23:34.450000    13/05/2010 13:34:45.560000    +01 01:11:11.110000
2    12/04/2009 12:33:35.460000    13/05/2010 13:34:45.560000    +396 01:01:10.100000

 

對相減的結果處理:

SELECT ENDTMT- BEGINTMT,substr((ENDTMT- BEGINTMT),instr((ENDTMT- BEGINTMT),' ')+7,2) seconds,
substr((ENDTMT- BEGINTMT),instr((ENDTMT- BEGINTMT),' ')+4,2)         minutes,
substr((ENDTMT- BEGINTMT),instr((ENDTMT- BEGINTMT),' ')+1,2)         hours,
trunc(to_number(substr((ENDTMT- BEGINTMT),1,instr(ENDTMT- BEGINTMT,' '))))  days,
trunc(to_number(substr((ENDTMT- BEGINTMT),1,instr(ENDTMT- BEGINTMT,' ')))/7) weeks
FROM timetest;

 

ENDTMT-BEGINTMT            SECONDS    MINUTES    HOURS    DAYS    WEEKS
+01 01:11:11.110000           11              11             01          1           0
+396 01:01:10.100000         10               01             01          396       56

 

INTERVAL YEAR TO MONTH資料類型:以下來源於 http://blog.chinaunix.net/u/19782/showart_212188.html

Oracle文法:
INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
該資料類型常用來表示一段時間差, 注意時間差只精確到年和月. 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
表示: 300個月, 注意該處MONTH的預設精度為3啊.(select INTERVAL '1300' MONTH(4)   from dual)
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個月

 

 

INTERVAL DAY TO SECOND資料類型 以下來源於http://blog.chinaunix.net/u/19782/showart_212191.html
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表示微秒.
範圍值:
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秒

 

和interval相關的函數:

NUMTODSINTERVAL(n, 'interval_unit')
將n轉換成interval_unit所指定的值, interval_unit可以為: DAY, HOUR, MINUTE, SECOND
注意該函數不可以轉換成YEAR和MONTH的.

select numtodsinterval(100,'DAY') + numtodsinterval(10,'MINUTE')  from dual;

 

NUMTOYMINTERVAL(n, 'interval_unit')
interval_unit可以為: YEAR, MONTH

select NUMTOYMINTERVAL(100,'YEAR') + NUMTOYMINTERVAL(10,'MONTH')  from dual;

 

兩個函數的結果不能進行加減操作:下面的操作不合法.

select  NUMTOYMINTERVAL(100,'YEAR')+ numtodsinterval(10,'MINUTE')  from dual;

 

對於時間的比較,今天比昨天大,今天減昨天為正數.