CODEWITHSUNDEEP
sqldatetime
KhawarAmeerMalik
KhawarAmeerMalik

Reputation: 93

Difference of datetime column in SQL

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

Upvotes: 3

Views: 57

Answers (3)

dnoeth
dnoeth

Reputation: 60502

Although Teradata doesn't support Standard SQL's LEAD it's easy to rewrite:

select tab.*,
   min(ts) over (order by ts rows between 1 following and 1 following) as next_ts
from tab
qualify 
   ts < next_ts - interval '1' hour

If you don't need to show the next timestamp:

select *
from tab
qualify 
   ts < min(ts) over (order by ts rows between 1 following and 1 following) - interval '1' hour

QUALIFY is a Teradata extension, but really nice to have, similar to HAVING after GROUP BY

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 4

sagi
sagi

Reputation: 40491

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)

Upvotes: 2

Related Questions