CODEWITHSUNDEEP
sqloracle-database
user1807807
user1807807

Reputation:

How to get difference between dates in the SAME column in oracle using only SQL

If I have a simple table with a column that has type DATE like this:

START_DATE
06/02/2013 05:30:00 AM
06/05/2013 12:00:00 PM
06/10/2013 01:45:00 AM

Is it possible to get the hours to the next START_DATE such as something like:

START_DATE                 HOURS_TO_NEXT_START
06/02/2013 05:30:00 AM     78.5
06/05/2013 12:00:00 PM     109.75
06/10/2013 01:45:00 AM     0

Note that this result is OK too:

START_DATE                 HOURS_FROM_PREVIOUS_START
06/02/2013 05:30:00 AM     0
06/05/2013 12:00:00 PM     78.5
06/10/2013 01:45:00 AM     109.75

Since at the end of the day the hours numbers are what I am really interested in...

I am pretty sure that CONNECT BY is involved in traversing this list recursively to check time difference to the next start date but I think I need help actually doing this. All the examples I see on CONNECT BY seem to address traversing a concurrent list.

Upvotes: 1

Views: 3797

Answers (2)

Devz
Devz

Reputation: 5

I think I just found an answer to group calc by ids

Just have to add the following

select start_date,
   (lead(start_date,1) over (*PARTITION BY ID* order by start_date)-start_date)*24 
        as hours_to_next_start
from myTable

Thanks again

Upvotes: 0

user359040
user359040

Reputation:

Try:

select start_date,
       (lead(start_date,1) over (order by start_date)-start_date)*24 
            as hours_to_next_start
from myTable

SQLFiddle here.

Upvotes: 5

Related Questions