Oracle 中时间的计算

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;

 

对于时间的比较,今天比昨天大,今天减昨天为正数.