Find The Time Difference Between Two Consecutive Rows in The Same Table in Sql

Calculate Time Difference Between Two Consecutive Rows

How about this:

select recordid, transdate,
       cast( (transdate - lag(transdate) over (order by transdate)) as time) as diff
from t;

In other words, you can subtract two datetime values and cast the result as a time. You can then format the result however you like.

Find the time difference between two consecutive rows in the same table in sql

I have solved this for similar problems and it need not be that the rows even be sorted:

select t1.EmpID, t1.TimeIn, t1.TimeOut, 
       datediff(minute, max(t2.TimeOut), t1.TimeIn) as minutes
from timesheet t1 left join timesheet t2 on t1.EmpID = t2.EmpID 
       and t2.TimeOut < t1.TimeIn
group by t1.EmpID, t1.TimeIn, t1.TimeOut

Let me know if this works.

Here is a sql fiddle: http://sqlfiddle.com/#!3/89a43/1

SQL: Difference between consecutive rows

You can use lag() to get the date of the previous order by the same customer:

select o.*,
    datediff(
        order_date,
        lag(order_date) over(partition by member_id order by order_date, order_id)
    ) days_diff
from orders o

When there are two rows for the same date, the smallest order_id is considered first. Also note that I fixed your datediff() syntax: in Hive, the function just takes two dates, and no unit.

I just don't get the logic you want to compute num_orders.

Calculate time difference between two consecutive rows, show only results greater than

Use lag():

SELECT user_id, TO_CHAR(dstamp, 'HH24:MI:SS') AS transaction_time,
       (dstamp - prev_stamp) * (24 * 60 * 60) as diff_seconds
FROM (SELECT it.*,
             LAG(dstamp) OVER (PARTITION BY user_id ORDER BY dstamp) as prev_dstamp
      FROM inventory_transaction it
     ) it
WHERE dstamp BETWEEN to_date('27/05/2021 18:00:00', 'dd/mm/yyyy hh24:mi:ss')  AND            
                 to_date('28/05/2021 06:00:00', 'dd/mm/yyyy hh24:mi:ss') 
ORDER BY user_id;

In Oracle it is easier to get the difference as seconds rather than in a time format.

Calculate Time Difference between consecutive rows based on an condition in SQL Server 2019

SELECT e.*, IIF(EventDescription like 'Pump STOP%',
    DATEDIFF(SECOND, Time, ISNULL((SELECT TOP 1 Time
                                    FROM @events 
                                    WHERE InfusionID = e.InfusionID 
                                        AND Time >= e.Time
                                        AND EventDescription like 'Pump Run%'
                                    ORDER BY Time ASC), GETDATE())), 0) as Duration
FROM @events e

@events is your [device_data] table. What the query does is it goes over all events and for each stop, looks for next start of the pump for the same InfusionID. If no start of the pump was found then it takes the current time because the pump must be stopped at the moment. Then it subtracts the 2 dates and returns the duration in seconds. I would highly suggest making a table with definitions of all EventDescriptions and using the ID of the description instead of the strings, because doing EventDescription like 'Pump Run%' instead of EventDescriptionID in (1,2,3) is expensive. This is the result:

Sql function find date difference between two consecutive rows per group

Based on @Akina hint I was able to get the required output from following query
Note:- My sessionDate column was in Date and timestamp format.

select
        t.PatientID,

        CAST(t.SessionTimestamp as Date) as SessionDate,

        LAG(t.SessionTimestamp) over(partition by t.PatientRecordId order by t.PatientID,t.SessionTimestamp) as Next_Date  

from mytable t

order by
t.PatientID,
t.SessionTimestamp

Timestamp difference between every consecutive row BIGQUERY SQL

LAG() is the right function to get the value from the previous row. You just need to use TIMESTAMP_DIFF() correctly:

select ID, DateTime,
       timestamp_diff(DateTime,
                      lag(DateTime, 1) OVER (ORDER BY DateTime), 
                      second
                     ) as TimeDiff
from `xxx.yyy.table`
order by DateTime;

Note that it looks like you want this per id. If so, you should PARTITION BY as well:

       timestamp_diff(DateTime,
                      lag(DateTime, 1) OVER (PARTITION BY id ORDER BY DateTime), 
                      second
                     ) as TimeDiff

Calculate average of time difference between consecutive row

We can use DATEDIFF along with LAG:

WITH cte AS (
    SELECT tran_end_time,
           LAG(tran_end_time) OVER (ORDER BY tran_end_time) AS tran_end_time_lag
    FROM yourTable
)

SELECT AVG(DATEDIFF(minute, tran_end_time_lag, tran_end_time)) AS diff_avg
FROM cte
WHERE tran_end_time_lag IS NOT NULL;

Note that the WHERE clause in the final query above ensures that we do not include any diff involving the earliest record.

---

Related Topics