如何在SQL中剔除夜间时间__快速结帖

Question

本帖最后由 comcn 于 2009-09-07 20:58:59 编辑

Answer 1

case when可以做吧
伪码：
select case when ontime < 08:00 then 08:00 
            when ontime > 22:00 then trunc(ontime)+1 08:00 
            else ontime end ontime,
       case when offtime < 08:00 then trunc(offtime)-1 22:00
            when offtime > 22:00 then 22:00
            else offtime end offtime 
from t1;外层嵌套相减，除去夜间10小时

Answer 2

楼主给的数据有错误。
如第三条记录，
2009-09-04 23:00    2009-09-07 07:00    14小时    开机时间以次日8点算，关机时间以前一日22点算5日8时到6日8时即14小时，7日7时关机，算做6日22时关机，6日8时到6日22时又14个小时，应该是28个小时。 select decode(sign(c),1,c*24-trunc(c)*10,0) from 
(
select 
      (  case when to_char(offtime,'hh24') <'08' then trunc(offtime,'dd')-1+22/24
            when to_char(offtime,'hh24') > '22' then trunc(offtime,'dd')+22/24
            else offtime 
        end -
        case when to_char(ontime,'hh24') <'08' then trunc(ontime,'dd')+1/3
            when to_char(ontime,'hh24') > '22' then trunc(ontime,'dd')+1+1/3
            else ontime 
        end
       ) c 
from tt )

Answer 3

假设表名为：timetest,参考sql 语句如下：
SELECT ontime,
       offtime,
   
       case
         when adj_offtime - adj_ontome < 0 then
          0
         else
          24 * (adj_offtime - adj_ontome)
       end -  10 * case
         when (adj_offtime - adj_ontome) < 0 then
          0
         else
          floor(adj_offtime - adj_ontome)
         
       end acthours
  FROM (select t.offtime,
               t.ontime,
               case
                 when to_number(to_char(t.offtime, 'HH24')) < 8 then
                  TO_DATE(to_char(t.offtime - 1, 'YYYY-MM-DD') || ':22',
                          'YYYY-MM-DD HH24')
                 when to_number(to_char(t.offtime, 'HH24')) > 22 then
                  TO_DATE(to_char(t.offtime - 1, 'YYYY-MM-DD') || ':22',
                          'YYYY-MM-DD HH24')
                 else
                  t.offtime
               end adj_offtime,
               case
                 when to_number(to_char(t.ontime, 'HH24')) < 8 then
                  TO_DATE(to_char(t.ontime, 'YYYY-MM-DD') || ':08',
                          'YYYY-MM-DD HH24')
                 when to_number(to_char(t.ontime, 'HH24')) > 22 then
                  TO_DATE(to_char(t.ontime + 1, 'YYYY-MM-DD') || ':08',
                          'YYYY-MM-DD HH24')
                 else
                  t.ontime
               end adj_ontome
        
          from timetest t) T

Answer 4

select ontime,offtime,
  round(case when to_char(ontime,'hh24mi')>to_char(offtime,'hh24mi')
    then (case when to_char(ontime,'hh24mi')<='0800' or to_char(offtime,'hh24mi')>='2200'
             or to_char(ontime,'hh24mi')>'2200' and to_char(offtime,'hh24mi')<='0800'
             then trunc(offtime-ontime)*14/24
           when to_char(ontime,'hh24mi')<='2200' and to_char(offtime,'hh24mi')<='0800'
             then trunc(offtime-ontime)*14/24+trunc(ontime)+22/24-ontime
           when to_char(ontime,'hh24mi')<='2200' and to_char(offtime,'hh24mi')>'0800' 
             then trunc(offtime-ontime+1)*14/24+trunc(ontime)-ontime+offtime-trunc(offtime)
           when to_char(ontime,'hh24mi')>'2200' and to_char(offtime,'hh24mi')<'2200'
             then trunc(offtime-ontime+1)*14/24+offtime-trunc(offtime)-8/24 end)
   else (case when to_char(offtime,'hh24mi')<='0800' or to_char(ontime,'hh24mi')>='2200'
            then trunc(offtime-ontime)*14/24
           when to_char(offtime,'hh24mi')<='2200' and to_char(ontime,'hh24mi')<='0800'
             then trunc(offtime-ontime)*14/24+offtime-trunc(offtime)-8/24
           when to_char(offtime,'hh24mi')<='2200' and to_char(ontime,'hh24mi')>'0800' 
             then trunc(offtime-ontime)*14/24+mod(offtime-ontime,1)
           when to_char(offtime,'hh24mi')>'2200' and to_char(ontime,'hh24mi')<='0800'
             then trunc(offtime-ontime+1)*14/24
           when to_char(offtime,'hh24mi')>'2200' and to_char(ontime,'hh24mi')<'2200'
             then trunc(offtime-ontime+1)*14/24+trunc(ontime)+22/24-ontime end)
   end*24)||'小时' times from t1

Answer 5

    create table testtime
  (ontime  date,
   offtime  date)
    select (offtime - ontime) * 24 - dd
  from (
  select decode(sign(to_char(ontime, 'hh24') - '08'),
                -1,
                ontime + (8 - to_char(ontime, 'hh24')) / 24,
                decode(sign(to_char(ontime, 'hh24') - '22'),
                       -1,
                       ontime,
                       ontime + (22 - to_char(ontime, 'hh24') + 10) / 24)) ontime,
         decode(sign(to_char(offtime, 'hh24') - '22'),
                -1,
                decode(sign(to_char(offtime, 'hh24') - '08'),
                       -1,
                       offtime + (8 - to_char(offtime, 'hh24')) / 24,
                       offtime),
                
                offtime - (to_char(offtime, 'hh24') - 22) / 24) offtime,
         floor(offtime - ontime) * 10 dd
    from testtime) aa