Difference of datetime column in SQL

Question

I have a table of 20000 records. each Record has a datetime field. I want to select all records where gap between one record and subsequent record is more than one hour [condition to be applied on datetime field].

can any one give me the SQL command code for this purpose.

regards

KAM

Answer 1

ANSI SQL supports the lead() function. However, date/time functions vary by database. The following is the logic you want, although the exact syntax varies, depending on the database:

select t.*
from (select t.*,
             lead(datetimefield) over (order by datetimefield) as next_datetimefield
      from t
     ) t
where datetimefield + interval '1 hour' < next_datetimefield;

Note: In Teradata, the where would be:

where datetimefield + interval '1' hour < next_datetimefield;

Answer 2

This can also be done with a sub query, which should work on all DBMS. As gordon said, date/time functions are different in every one.

SELECT t.* FROM YourTable t
WHERE t.DateCol + interval '1 hour' < (SELECT min(s.DateCol) FROM YourTable s
                   WHERE t.ID = s.ID AND s.DateCol > t.DateCol)

You can replace this:

t.DateCol + interval '1 hour'

With one of this so it will work on almost every DBMS:

DATE_ADD( t.DateCol, INTERVAL 1 hour)
DATEADD(hour,1,t.DateCol)