I am trying to filter transactions that are less than 5 minutes apart. In trying to develop this filter, I keep running into error messages. The two columns I need to find the difference for are TIME datatypes:
(SEC.CALL_START_TM-PRI.CALL_END_TM) AS TIME_DIFF
The above does not work. I've tried casting into a varchar, and time format hh:mm:ss to subtract. Nothing seems to be working. How would you write the statement to return a result that I can use WHERE TIME_DIFF <= '00:05:00'?
The difference between two timestamps is an interval:
(SEC.CALL_START_TM-PRI.CALL_END_TM) interval day(4) to second AS TIME_DIFF
WHERE TIME_DIFF <= interval '5' minute
Interesting! Thanks, Dieter.
It looks like it's expecting something between ) and INTERVAL. I'm not too familiar with this function...do you know what that might be?
Ops, the INTERVAL is not needed, just remove it:
(SEC.CALL_START_TM-PRI.CALL_END_TM) day(4) to second AS TIME_DIFF
I had to add in a filter that said the time difference was also greater than 0...but it worked perfectly!
Thank you so much, Dieter. That one was really stumping me.