All Forums Database
ciw916 8 posts Joined 11/12
21 May 2013
Minute Difference Between Time Datatypes

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'?
 
 

dnoeth 4628 posts Joined 11/04
21 May 2013

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
Dieter

Dieter

ciw916 8 posts Joined 11/12
21 May 2013

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?

dnoeth 4628 posts Joined 11/04
21 May 2013

Ops, the INTERVAL is not needed, just remove it:
(SEC.CALL_START_TM-PRI.CALL_END_TM) day(4) to second AS TIME_DIFF
 
Dieter

Dieter

ciw916 8 posts Joined 11/12
22 May 2013

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. 

You must sign in to leave a comment.