DB2 SQL Finding rows with a gap of 1 minute with other rows

Question

DB2 v10.5.0.5 on Linux

I have a table of something like:

LOG_IN              | LOG_OFF
-----------------------------------------
2017-01-22 08:00:00 | 2017-01-22 09:00:00
2017-01-22 09:01:00 | 2017-01-22 10:00:00
2017-01-22 10:00:00 | 2017-01-22 10:15:00
2017-01-22 10:15:00 | 2017-01-22 10:45:00
2017-01-22 11:00:00 | 2017-01-22 11:29:00
2017-01-22 11:30:00 | 2017-01-22 12:00:00

I would like to select the rows where it has a gap of 1 minute with other rows.

Desired result:

LOG_IN              | LOG_OFF
-----------------------------------------
2017-01-22 08:00:00 | 2017-01-22 09:00:00
2017-01-22 09:01:00 | 2017-01-22 10:00:00
2017-01-22 11:00:00 | 2017-01-22 11:29:00
2017-01-22 11:30:00 | 2017-01-22 12:00:00

Answer 1

In Db2 you can use date / time arithmetics to do that. Without testing, something like this should work:

select log_in, log_off
from accountlogs
where log_off - log_in = 1 minute

Instead of 1 minute you can also use other values or < instead of =.

If you want to find gaps with other rows, you need to self-join:

select al1.log_in, al1.log_off, al2.log_in, al2.log_off
from accountlogs al1, accountlogs al2
where al1.log_off - al2.log_in < 1 minute

The above would fit your desired result and also returns data of the matching other row.