select statement for missing hour and mintute for a given time interval

Hi

I have  column called  time_interval  the format is 'dd/mm/yyyy hh24:mi:ss'
every  day  the data will be coming in , for  every hour, min, sec
some days the data will be missing ..
here i want to write select statement to find the missing hours in a give day .
or missing  hours for a given interval  period of month
 like i want to find the missing hours  
for the month '01-Feb-2007 :00:00:00 to 28-Feb-2007 :23:00:00'
so it should display all the missing hours of that  particular month.
Please assist ,

Thanks in Advance
Raj


nrajasekhar7Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
This query works for me perfectly. See the attached document for you to understand how this works.

Can you try :

select to_char(mydd,'dd-mon-yyyy hh24:mi:ss') missing_hours
from ( select level mylvl , to_date('15-jun-2010','dd-mon-yyyy') + ( ( level  - 1 ) / 24 ) mydd
from dual
connect by level <= 24 * (to_date('15-jun-2010 23:59:59','dd-mon-yyyy hh24:mi:ss') -
to_date('15-jun-2010 00:00:00','dd-mon-yyyy hh24:mi:ss')) ) x
where not exists ( select 1 from my_table t where trunc(t.my_date,'hh24') = trunc(x.mydd,'hh24') )
order by mydd ;

Thanks
missing-hours.doc
0
 
AbarajCommented:
try this
'01-Feb-2007 :00:00:00 to 28-Feb-2007 :23:59:59'
0
 
Muhammad KhanManager, ITCommented:
try this


select day_part, hour_part, day_part || ' ' || hour_part merged_datetime
  from (select to_char(trunc(sysdate) + (level - 1) / 24, 'hh24:mi:ss') hour_part
          from dual
        connect by level <= 24),
       (select to_char(to_date(&p_from, 'DD-MON-RRRR HH24::mi:ss') + level,
                       'dd-MON-RRRR') day_part
          from dual
        connect by level <= (to_date(&p_to, 'DD-MON-RRRR hh24:mi:ss') -
                   to_date(&p_from, 'DD-MON-RRRR hh24:mi:ss')))
 where to_date(day_part || ' ' || hour_part, 'DD-MON-RRRR hh24:mi:ss') not in
       (select trunc(time_interval, 'HH')
          from your_table
         where time_interval between &p_from and &p_to)
 order by day_part,hour_part

Open in new window

0
 
SharathData EngineerCommented:
try this.
replace the hard coded date values as per your requirement or pass the date as parameter.

SELECT t2.Start_Date 
FROM   (SELECT Start_Date 
        FROM   (SELECT CAST(TO_DATE('01-Feb-2007 00:00:00','DD-Mon-YYYY hh24:mi:ss') - 1 / 24 + ROWNUM / 24 AS TIMESTAMP) AS Start_Date, 
                       CAST(TO_DATE('02-Feb-2007 00:00:00','DD-Mon-YYYY hh24:mi:ss') AS TIMESTAMP)                        AS End_Date 
                FROM   cat) t1 
        WHERE  Start_Date < End_Date) t2 
       LEFT JOIN MyTable t3 
         ON TRUNC(t2.Start_Date,'HH') = TRUNC(t3.time_interval,'HH') 
WHERE  t3.time_interval IS NULL

Open in new window

0
 
nrajasekhar7Author Commented:
select day_part, hour_part, day_part || ' ' || hour_part merged_datetime
  from (select to_char(trunc(sysdate) + (level - 1) / 24, 'hh24:mi:ss') hour_part
          from dual
        connect by level <= 24),
       (select to_char(to_date(&p_from, 'DD-MON-RRRR HH24::mi:ss') + level,
                       'dd-MON-RRRR') day_part
          from dual
        connect by level <= (to_date(&p_to, 'DD-MON-RRRR hh24:mi:ss') -
                   to_date(&p_from, 'DD-MON-RRRR hh24:mi:ss')))
 where to_date(day_part || ' ' || hour_part, 'DD-MON-RRRR hh24:mi:ss') not in
       (select trunc(time_interval, 'HH')
          from your_table
         where time_interval between &p_from and &p_to)
 order by day_part,hour_part

giving error saying  ORA-01861 literal does not match please suggest
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.