1

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
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
turkongwalayr
  • 33
  • 1
  • 7

2 Answers2

0

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.

data_henrik
  • 16,724
  • 2
  • 28
  • 49
  • Hi Henrik. Duration gap comparison must be with **other** rows. – turkongwalayr Dec 20 '17 at 15:34
  • Then it is a self-join, but the arithmetics is the same. Leave out the columns that you don't need – data_henrik Dec 20 '17 at 15:40
  • I don't know what made me do all the suffering in not thinking of a simple join. Lost hours. Thank you Henrik. My final solution though is timestampdiff(4, char(al2.LOG_OFF - al1.LOG_IN)) = -1 – turkongwalayr Dec 20 '17 at 16:41
0

Given your version of LUW, the LAG function should also be available:

SELECT log_in, log_off
FROM (SELECT log_in, log_off, (LAG(log_off) OVER (ORDER BY log_in)) - 1 MINUTE AS previous
      FROM Data) d
WHERE log_in = previous

Fiddle Example (SQL Server)

This might be faster than a self-join (you'll need to check): the date math will cause the optimizer to ignore any indices, which may be a significant performance penalty on large tables. However, note that this limits you to exactly one previous row, which may not be what you want (which would require a self-join to find all matching rows).

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45