0

i need simple SQL to calculate the time diff between Two Consecutive Rows anyone can help

enter image description here

6
  • 3
    Please update your question and specify your RDBMS (SQL Server, Oracle, etc.). Also, please elaborate on your definition of time diff. Does that mean days, hours, minutes, etc.)? Commented Oct 8, 2018 at 12:38
  • i'm using TOAD for oracle and the time diff needed her should be in minutes
    – Mr.UNK
    Commented Oct 8, 2018 at 12:43
  • 1
    Which version of oracle as there are several supported versions currently.
    – Joe W
    Commented Oct 8, 2018 at 12:50
  • 2
    Define: "consecutive row". Also, how are groups of rows identified? Commented Oct 8, 2018 at 12:55
  • 1
    TOAD is not a DBMS system. It's a SQL client tool and the Toad version is irrelevant. What Joe W. was asking for is the version of your Oracle database server.
    – user1822
    Commented Oct 8, 2018 at 12:59

2 Answers 2

5

You can use a window function (aka "Analytic Function") to access values in the rows outside of the "current" row.

If you do that, it is mandatory to specify a criteria that defines the sort order of the rows otherwise there isn't such a thing as "the previous rows".

Given the extremely limited information you have shown us, you are probably looking for something like the following:

select subno,
       contrno, orderby, prepost_paid, 
       time_stamp, 
       lag(time_stamp) over (order by time_stamp) - time_stamp as timestamp_diff
from the_table
order by time_stamp;
1
  • Thanks a lot, yes this is what i need, forgive me for being new beginner in using SQL :) that's why i was unable to provide detailed information
    – Mr.UNK
    Commented Oct 8, 2018 at 13:15
0

It's hard to understand what you are asking.

I presume you want the amount of time taken between selecting two rows like this:

select hsecs from v$timer;
select row 1;
select hsecs from v$timer;
select row 2;
select hsecs from v$timer;

The value of hsecs is in centiseconds so you can do the conversions to work out the minutes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.