How do I find transactions that are less than 10 minutes apart using timestamps in sql

Question

I have only gone so far:

select timestamp, trans, Count(trans)  
From(
    Select to_char(CREATED_TIMESTAMP) as timestamp,SOURCE_MSISDN||DEST_MSISDN||AMOUNT as trans
    From ADMDBMC.TRANSACTION_CASH
    WHERE TO_date(CREATED_TIMESTAMP) > = '1-sep-2014' AND TO_date(CREATED_TIMESTAMP) < '2-sep-2014'
    and STATUS_DESCRIPTION='SUCCESS'
    ) 

group by timestamp,trans
Having count(trans)>1
order by count(trans) desc

Why are you applying the `to_date()` function on a column that is (apparently) already a `date` (or `timestamp`). All that you are doing is to convert the `date` to a `varchar` and then convert it back to a `date`. That's totally useless. — a_horse_with_no_name, Sep 10 '14 at 11:15
I will correct that @Zeshan I want the query to only give me results of transaction less than 10 minutes apart. — Kax, Sep 10 '14 at 11:21
did you mean from 1-sep-2014 00:00 to 1-sep-2014 00:10? @Kax — Zeshan Khan, Sep 10 '14 at 11:30
You want to include any transaction in your result provided you can also find another transaction - no more that 10 minutes apart? — Jens Krogsboell, Sep 10 '14 at 11:31

Emmanuel · Accepted Answer · 2014-09-10T13:02:20.787

I think you can achieve that by truncating your timestamp on the nearest multiple of 10 minutes: you could replace to_char(CREATED_TIMESTAMP) as timestamp with regexp_replace(to_char(CREATED_TIMESTAMP, 'dd-Mon-yyyy hh24:mi'), '.$', '0') and I think your group by would then be ok.

EDIT : the previous solution was only working if the 2 transactions were part of the same multiple of 10 minutes. Here is a better one:

Select *
From
(
    Select CREATED_TIMESTAMP,
           SOURCE_MSISDN||DEST_MSISDN||AMOUNT as trans,
           lag(CREATED_TIMESTAMP, 1, null) over (partition by SOURCE_MSISDN||DEST_MSISDN||AMOUNT order by CREATED_TIMESTAMP) as PREVIOUS_TIMESTAMP
    From ADMDBMC.TRANSACTION_CASH
    Where TO_date(CREATED_TIMESTAMP) >= '1-sep-2014'
      And TO_date(CREATED_TIMESTAMP) < '2-sep-2014'
      And STATUS_DESCRIPTION='SUCCESS'
) 
where CREATED_TIMESTAMP <= PREVIOUS_TIMESTAMP + INTERVAL '10' MINUTE

The lag analytical function allows to get for every row the timestamp of preceeding row of the same group identified by the concatenation of SOURCE_MSISDN / DEST_MSISDN / AMOUNT. Then once we have this, we just have to check the difference.

I want to check the time in between time stamps.. if the time is less than 10 min then return the result.. so for example if first time time stamp is 1:03 AM and next time stamp is 1:12 AM then then that transaction should appear in the resuts — Kax, Sep 10 '14 at 12:05

score 0 · Answer 2 · edited May 23 '17 at 12:21

Emmanuels way would return false negative for example if one transaction occured at 11:49 and second at 11:52

You could use LEAD or LAG aggregate function with window option to get timestamp of a older row then current and calculate difference between two timestamps or COUNT(*) with window, for example:

select * FROM ( Select to_char(CREATED_TIMESTAMP) as timestamp,SOURCE_MSISDN||DEST_MSISDN||AMOUNT as trans ,COUNT(*) OVER (PARTITION BY SOURCE_MSISDN||DEST_MSISDN||AMOUNT ORDER BY CREATED_TIMESTAMP RANGE NUMTODSINTERVAL(600,'SECOND') PRECEDING) L_OF_TRANS From ADMDBMC.TRANSACTION_CASH WHERE TO_date(CREATED_TIMESTAMP) > = '1-sep-2014' AND TO_date(CREATED_TIMESTAMP) < '2-sep-2014' and STATUS_DESCRIPTION='SUCCESS' ) WHERE L_OF_TRANS > 1

tbone · Answer 3 · 2014-09-10T14:46:18.507

0

You should try using to_dsinterval function. Subtracting timestamps gives intervals, so you can compare timestamps using to_dsinterval. For example:

select *
from my_table x
where x.timestamp1 - x.timestamp2 < to_dsinterval ('00 00:10:00');

In this example, timestamp1 would be the more recent timestamp.

edited Sep 10 '14 at 14:46

answered Sep 10 '14 at 13:44

tbone

14,580
3
32
40

Thanks for this... I did not know about this function. – Kax Sep 10 '14 at 14:47
If it satisfies your question, feel free to accept the answer, thanks Kax. – tbone Sep 10 '14 at 17:51

How do I find transactions that are less than 10 minutes apart using timestamps in sql

3 Answers3