missing hours in 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.

sdstuberCommented:
so  time_interval is a string,  not a date?

The reason I ask is you say your data has a format,  dates don't have formats, only strings do.

assuming your data is strings then this should work
SELECT hr
  FROM (SELECT TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24
                   hr
          FROM all_objects  -- use any big table, something with at least as many rows as hours in your range
         WHERE TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24 <=
                   TO_DATE(
                       '28-Feb-2007 :23:00:00',
                       'dd-mon-yyyy :hh24:mi:ss'
                   ))
 WHERE hr NOT IN
           (SELECT TRUNC(
                       TO_DATE(time_interval, 'dd/mm/yyyy hh24:mi:ss'),
                       'hh'
                   )
              FROM your_table);

Open in new window

0
sdstuberCommented:
if your data is actually of date type
then you can skip the conversion on the NOT IN clause
SELECT hr
  FROM (SELECT TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24
                   hr
          FROM all_objects -- use any big table, something with at least as many rows as hours in your range
         WHERE TO_DATE('01-Feb-2007 :00:00:00', 'dd-mon-yyyy :hh24:mi:ss')
               + (ROWNUM - 1) / 24 <=
                   TO_DATE(
                       '28-Feb-2007 :23:00:00',
                       'dd-mon-yyyy :hh24:mi:ss'
                   ))
 WHERE hr NOT IN (SELECT TRUNC(time_interval, 'hh') FROM your_table);

Open in new window

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
HainKurtSr. System AnalystCommented:
I suggest create a table say N(d int), put 0,1,2,3,4,5,6,7,8,9 inside this table

create N4 as
select n1.d*1000 + n2.d*100 + n3.d*10 + n4.d as d from n n1, n n2, n n3, n n4

so when you say

select * from n4 where d<=366 : 0 .. 366
select * from n4 where d<=24 : 0 .. 24

we can use these in your queries

now

select date1 + DD.d + HH.h as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH

will give you all hour intervals for given dates : date1 & date2

say above query is Q

now we can join Q & yourTable

select datetime1, datetime2, (select count(1) from yourtable t where t.interval in datetime1 and datetime2) as r
from (Q)
order by datetime1

this query will give you all intervals & counts: you are asking for records where r = 0

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HainKurtSr. System AnalystCommented:
fix to above: N4 is a view as

create view N4 as
select n1.d*1000 + n2.d*100 + n3.d*10 + n4.d as d from n n1, n n2, n n3, n n4
0
HainKurtSr. System AnalystCommented:
one more fix ;)

select date1 + DD.d + HH.h as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH

-->

select date1 + DD.d + (HH.h/24) as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH
0
HainKurtSr. System AnalystCommented:
I hate this, one more fix

select date1 + DD.d + (HH.h/24) as datetime1,  date1 + DD.d + (HH.h+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=24) as HH

-->

select date1 + DD.d + (HH.d/24) as datetime1,  date1 + DD.d + (HH.d+1)/24 as datetime2
from
  (select * from n4 where d<=cast(date2-date1 as int)) as DD,
  (select * from n4 where d<=23) as HH
0
sdstuberCommented:
if you're only going to allow 9999 rows, why not simply use a table that already has that many.
yes, you could extend your view by creating as many joins as needed but a simple one column table of a million nulls  only takes up about 13Mb and can generate the rownum list easy enough.

Of course in versions 9i and above we'd use other methods like

(select level n from dual connect by level <= 1000000)

those and a simply NOT IN should be MUCH more efficient than all those joins and counting.
it will work, but a lot of hoops to jump through
0
HainKurtSr. System AnalystCommented:
I am using 9.2 and

select level n from dual connect by level <= 1000000

is giving me only one record...

N
1

0
sdstuberCommented:
you are encountering a bug,  are you by chance running 9.2.0.2?
0
sdstuberCommented:
one of the workarounds to the bug is to nest it in another inline view

select n from (select level n from dual connect by level <= 1000000)

0
HainKurtSr. System AnalystCommented:
Oracle9i Enterprise Edition 9.2.0.8
0
sdstuberCommented:
I've never had a problem with the connect by query in anything from 9.2.0.4 through 11.2.0.1

Either you are only fetching 1 row from the cursor, or you have encountered a bug.
It should work.   I recommend contacting oracle support.

However, this question is for 8i (at least according to the selected zone) and it definitely isn't supported there, hence the recommendation to use a large table.
0
nrajasekhar7Author Commented:
Thanks a lott...
0
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.