[Oracle] 一个求汇总时间的问题. . . 求教!

Question

本帖最后由 Lin_ms 于 2013-01-08 16:54:34 编辑

Answer 1

带小数的
 select to_date(substr('20130103200000',1,4)||
'-'||substr('20130103200000',5,2)||'-'||substr('20130103200000',7,2)||
' '||substr('20130103200000',9,2)||':'||substr('20130103200000',11,2)||
':'||substr('20130103200000',13,2),'yyyy-mm-dd hh24:mi:ss') - 
 to_date(substr('20130102080000',1,4)||
'-'||substr('20130102080000',5,2)||'-'||substr('20130102080000',7,2)||
' '||substr('20130102080000',9,2)||':'||substr('20130102080000',11,2)||
':'||substr('20130102080000',13,2),'yyyy-mm-dd hh24:mi:ss') from dual;
取整的select trunc(to_date(substr('20130103200000',1,4)||
'-'||substr('20130103200000',5,2)||'-'||substr('20130103200000',7,2)||
' '||substr('20130103200000',9,2)||':'||substr('20130103200000',11,2)||
':'||substr('20130103200000',13,2),'yyyy-mm-dd hh24:mi:ss') - 
 to_date(substr('20130102080000',1,4)||
'-'||substr('20130102080000',5,2)||'-'||substr('20130102080000',7,2)||
' '||substr('20130102080000',9,2)||':'||substr('20130102080000',11,2)||
':'||substr('20130102080000',13,2),'yyyy-mm-dd hh24:mi:ss')) from dual;
把那个换成具体的字段就行了。这是我的做法。

Answer 2

是不是应该也给定个起始时间，比如v_beginTimeselect v_endTime - v_beginTime - sum(time_t - time_s)
from tableA

Answer 3

你时间工时的SQL吧？
如果是这样就不需要如此复杂了，
select sum(to_date(time_t,'yyyymmddhh24miss')-to_date(time_s,'yyyymmddhh24miss')) from 目标表
当然你可能要计算到当前时间：
select sum(to_date(case when time_t is null then
                      to_char(sysdate,'yyyymmddhh24miss')
                     else
                      time_t
                   end,
                   'yyyymmddhh24miss') -
           to_date(time_s, 'yyyymmddhh24miss'))
  from 目标表