Difference in time between records

sql oracle date-arithmetic
AndyDan picture AndyDan · Nov 29, 2010 · Viewed 10.7k times · Source

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

Answer

OMG Ponies picture OMG Ponies · Nov 29, 2010

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

Related questions

Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More
Oracle SQL Where clause to find date records older than 30 days

I want to find records in a (Oracle SQL) table using the creation date field where records are older than 30 days. It would be nice to find records using a operators like > but if anyone can suggest quick SQL …

Read More