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;
对于时间的比较,今天比昨天大,今天减昨天为正数.