要求我编写PC文件处理数据库里面一张表里的data_time这个字段的值,这个值是以yyyymmddhhmisst这样的格式存放的,就像这样200711221246576的值,现在要求对这个字段的值进行加减时间的操作,然后重新更新,要求能够处理加减yyyy或者yy或者mm或者dd或者hh或者hhh或者mi或者ss或者t这几种格式的操作,求各位给点方法,怎么来处理这个问题?不胜感激!
调试欢乐多
numtodsinterval(15000, 'MINUTE')
numtodsinterval(150000, 'SECOND')
2 ;已更新 1 行。SQL> update t set a=a+numtodsinterval(1,'hour') --加一小时
2 ;已更新 1 行。SQL> update t set a=a+numtodsinterval(1,'second') --加一秒
2 ;已更新 1 行。SQL> update t set a=a+numtodsinterval(500/1000,'second') --加500毫秒
2 ;已更新 1 行。SQL> select * from t;A
---------------------------------------------------------------------------
2008-02-28 12:35:34.546000
学到了,可以到微秒:
SELECT systimestamp,systimestamp+numtodsinterval(1/1000000,'second')
FROM dual;
/* 12小时制 */
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtoyminterval(1,'year'),'YYYYMMDDHHMISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtoyminterval(1,'month'),'YYYYMMDDHHMISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtodsinterval(1,'day'),'YYYYMMDDHHMISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtodsinterval(1,'hour'),'YYYYMMDDHHMISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtodsinterval(1,'minute'),'YYYYMMDDHHMISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtodsinterval(1,'second'),'YYYYMMDDHHMISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHHMISSFF')+numtodsinterval(5/10,'second'),'YYYYMMDDHHMISSFF1');
/* 24小时制 */
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtoyminterval(1,'year'),'YYYYMMDDHH24MISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtoyminterval(1,'month'),'YYYYMMDDHH24MISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtodsinterval(1,'day'),'YYYYMMDDHH24MISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtodsinterval(1,'hour'),'YYYYMMDDHH24MISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtodsinterval(1,'minute'),'YYYYMMDDHH24MISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtodsinterval(1,'second'),'YYYYMMDDHH24MISSFF1');
update TESTTIME set time=to_char(TO_TIMESTAMP(time, 'YYYYMMDDHH24MISSFF')+numtodsinterval(5/10,'second'),'YYYYMMDDHH24MISSFF1');
然后再用EXECUTE语句执行,搞定,因为开始我就是想到先用select语句导出来用C来处理,但是觉得有点恐怖,所以一直在试直接用数据库语句来执行,多谢codearts兄提供的思路和方法,很有收获!