比较笨的方法 try: select (to_date(substr('20060612192456674',1,14),'yyyymmddhh24miss')- to_date(substr('20060612192455674',1,14),'yyyymmddhh24miss'))*24*60*60+ (substr('20060612192456674',-1,3)-substr('20060612192455674',-1,3)) from dual;
首先执行 alter session set nls_timestamp_format = 'yyyymmddhh24missff3' 然后 select (cast('20060612192456674' as timestamp(3))-cast('20060612192455674' as timestamp(3))) from dual 可以得出如下形式的结果 +000000000 00:00:01.000 前面+000000000表示相差的天数 后面是00:00:01.000 相差几小时,几分,几秒,几毫秒然后就可以算出来共多少毫秒了,用substr然后乘以24,60,1000之类? 没有找到好的办法 关注
try:
select (to_date(substr('20060612192456674',1,14),'yyyymmddhh24miss')-
to_date(substr('20060612192455674',1,14),'yyyymmddhh24miss'))*24*60*60+
(substr('20060612192456674',-1,3)-substr('20060612192455674',-1,3)) from dual;
然后 select (cast('20060612192456674' as timestamp(3))-cast('20060612192455674' as timestamp(3))) from dual
可以得出如下形式的结果
+000000000 00:00:01.000
前面+000000000表示相差的天数
后面是00:00:01.000 相差几小时,几分,几秒,几毫秒然后就可以算出来共多少毫秒了,用substr然后乘以24,60,1000之类?
没有找到好的办法
关注
上面是算出秒
+ (TO_NUMBER(SUBSTR('20060612192456674',-3 , 3)) - TO_NUMBER(SUBSTR('20060612192456674', -3 , 3)))/1000
这里是算出微秒转化后的秒。
我现在是这样解决的了。