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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Naveen KumarProduction 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.