Difference between timestamps greater than 2 minutes

[#1]

Hello,

I have a database that records a date and timestamp against every transaction and essentially I want to flag up and sum (over a day, week etc) the amount of time between transactions when the difference between them is more than 2 minutes. See below example.

Transaction	Timestamp	Difference
1	2012-06-08 12:09:12
2	2012-06-08 12:10:12	00:01:00
3	2012-06-08 12:14:12	00:04:00
4	2012-06-08 12:15:12	00:01:00
5	2012-06-08 12.26:12	00:11:00
6	2012-06-08 12:45:12	00:19:00
7	2012-06-08 12:46:12	00:01:00

In this example the total difference between timestamps is 37 minutes, however I don't care about when the difference is less than 2 minutes so don't require 3 of the transactions so the difference I want to see is 34 minutes. I'm thinking that using peek and an if statement in the script will work but any other suggestions?

Thanks,

Ralph

[#2]

Hi Ralph,

that table above is the input?

And the output should be

Transaction	Timestamp	Difference
1	2012-06-08 12:09:12
3	2012-06-08 12:14:12	00:04:00
5	2012-06-08 12.26:12	00:11:00
6	2012-06-08 12:45:12	00:19:00

?

In the script I would use

LOAD Transaction, Timestamp, Difference

from ...

where Difference > Timestamp#('00:02:00', 'hh:mm:ss');

Ciao

Klaus

[#3]

Hi Klaus,

This would work although I need to include all other transactions as well so I would need to essentially add a flag when the difference is greater than 2 minutes as well as summing the differences.

Thanks,

Ralph

[#4]

Ok Ralpf,

for the flag take

LOAD

     IF(Difference > Timestamp#('00:02:00', 'hh:mm:ss'), 1, 0) as relevant_difference,

....

and where do you want to store the sum

or do you need the sum in some chart?

Ciao

Klaus