sql - Find Consecutive Records with Timestamp Interval > 15 mins -

sql - Find Consecutive Records with Timestamp Interval > 15 mins -

a process runs (is supposed run) every 15 mins , add together , entry ms sql 2012 logging table.

2013-02-07 10:07:47.000 2013-02-07 09:52:36.000 2013-02-07 09:37:34.000 2013-02-07 09:22:32.000 2013-02-07 09:07:31.000 2013-02-07 08:52:29.000 2013-02-07 08:37:28.000 2013-02-07 08:13:53.000 2013-02-07 07:58:51.000 2013-02-07 07:43:50.000 2013-02-07 07:28:48.000 2013-02-07 07:13:47.000 2013-02-07 06:58:37.000

i need help query homecoming consecutive timestamps > 16 minutes apart. in above records, query homecoming 2013-02-07 08:37:28.000 , 2013-02-07 08:13:53.000

edit - add together reference ms sql

the reply question much depends 1 rdbms using.

something should work sql server using datediff , row_number:

with cte ( select processtime, row_number() on (order processtime desc) rn process ) select p.processtime, p2.processtime processtime2, datediff(mi, p.processtime, p2.processtime) cte p bring together cte p2 on p.rn = p2.rn + 1 datediff(mi, p.processtime, p2.processtime) > 16

it's putting dates in 2 separate columns -- easy plenty set in single column if preferred.

here fiddle.

if allow know rdbms, set alternative solution if needed.

for oracle, utilize extract( min p2.processtime - p.processtime ) vs datediff - rest of query should work -- here fiddle.

for mysql, code little different mysql doesn't back upwards ctes or row_number. should in right direction.

good luck.

sql sql-server timestamp sql-server-2012 intervals