0

I have a table with millions of records containing a date/time stamp - I am trying to return records that have a difference > a given number of seconds.

The table contains a date/time stamp, but I am unable to use the where and the calculated difference. (createddate is date field and updatedon is date/time)

SELECT createddate,
       LEAD(createddate, 1) OVER (ORDER BY createddate) AS created_next,
       LEAD(createddate, 1) OVER (ORDER BY createddate) - createddate AS created_diff, 
       LEAD(updatedon, 1) OVER (ORDER BY updatedon) AS created_next,
       LEAD(createddate, 1) OVER (ORDER BY updatedon) - updatedon AS Updatedon_diff
FROM   gsdaudit
--WHERE created_diff >  1000
ORDER BY updatedon_diff
pilcrow
  • 56,591
  • 13
  • 94
  • 135
Tanton
  • 1
  • 2
  • 2

1 Answers1

1

The simplest way (assuming SQL server) is probably to use DATEDIFF to get the difference in seconds and put the calculations inside a common table expression;

WITH cte AS (
  SELECT 
    createddate,
    DATEDIFF(second, createddate, 
             LEAD(createddate, 1) OVER (ORDER BY createddate)) created_diff,
    updatedon,
    DATEDIFF(second, createddate, 
             LEAD(updatedon,   1) OVER (ORDER BY updatedon  )) updatedon_diff
  FROM   gsdaudit
)
SELECT * FROM cte 
WHERE created_diff >  10 OR updatedon_diff > 10

An SQLfiddle to test with.

EDIT: The same query for Oracle;

WITH cte AS (
  SELECT 
    createddate,
    (LEAD(createddate, 1) OVER (ORDER BY createddate)-createddate)*24*60*60 created_diff,
    updatedon,
    (LEAD(updatedon, 1) OVER (ORDER BY updatedon)-updatedon)*24*60*60 updatedon_diff
  FROM   gsdaudit
)
SELECT * FROM cte 
WHERE created_diff >  10 OR updatedon_diff > 10

Another SQLfiddle.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Thanks for the information - I am working in oracle, do you have any information on what I can use to replace the datediff funtions, or an example would be really great. – Tanton Aug 05 '13 at 20:47