Oracle SQL

[daviddmw]

Hi there,

I hope you expert could help me figure out how to make a query within same Oracle table. I spent quite some time but still have no clue. Details of my question as follows:

Assume a table contains value in datetime (YYYYMMDDHH24) format:
2006101101
2006101102
2006101104
2006101105
2006101106
2006101110
...

I need to compare two records at a time to get the difference in hour between the two records. The expected result looks like:
begin_time end_time gap_of_hours
2006101101 2006101102 1
2006101102 2006101104 2
2006101104 2006101105 1
2006101105 2006101106 1
2006101106 2006101110 4
...

Can anyone helps me how to do it?

Many thanks!

Cheers,
David

 

[MikeM]

you can subtract one date from another then extract the days and hours from the interval returned e.g.

declare
  date1 timestamp := to_timestamp('02-JAN-2007 12:00',
                                  'DD-MON-YYYY HH24:MI');
  date2 timestamp := to_timestamp('01-JAN-2007 13:00',
                                  'DD-MON-YYYY HH24:MI');
  hours number;
begin
  select extract(day from(date1 - date2)) * 24 +
         extract(hour from(date1 - date2))
    into hours
    from dual;
  dbms_output.put_line('Hours difference: ' || hours);
end;

possibly easier ways but this is the method I've used previously

 

[ed10k]

-- LEAD is an analytic function
select
begin_time
,end_time
,(to_date(end_time, 'YYYYMMDDHH24') - to_date(begin_time, 'YYYYMMDDHH24')) * 24 gap_of_hours
from ( select
c1 begin_time
,lead(c1) over (order by c1) end_time
from test
)
;

 

[daviddmw]

LEAD really helps me to construct the following SQL:-

select to_date(p_dtm,'YYYYMMDDHH24') as p_dtm,
lead(to_date(p_dtm,'YYYYMMDDHH24'),1,null) over (order by to_date(p_dtm,'YYYYMMDDHH24')) as next_p_dtm,
(lead(to_date(p_dtm,'YYYYMMDDHH24'),1,null) over (order by to_date(p_dtm,'YYYYMMDDHH24')) - to_date(p_dtm,'YYYYMMDDHH24')) * 24 as p_dtm_diff
from (
)
where p_dtm_diff > 1

Thank you all for your kind help.

Have a nice day!

David