Search code examples
sqloracledate-arithmetic

Difference in time between records

I have a table that has (among others) a timestamp column (named timestamp; it's a standard Oracle DATE datatype). The records are about 4-11 minutes apart, about 7 or 8 records every hour, and I'm trying to determine if there is any pattern to them.

Is there an easy way to see each record, and the number of minutes that record occurred after the previous record?

Thanks, AndyDan

Solution

  • This is Oracle 9i+, using the LAG function to get the previous timestamp value without needing to self join:

    SELECT t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp) AS diff
  FROM YOUR_TABLE t

    ...but because whole numbers represent the number of days in the result, a difference of less than 24 hours will be a fraction. Also, the LAG will return NULL if there's no earlier value -- same as if having used an OUTER JOIN.

    To see minutes, use the ROUND function:

    SELECT ROUND((t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp)) *1440) AS diff_in_minutes
  FROM YOUR_TABLE t