Minute Difference Between Time Datatypes

Database
Enthusiast

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

4 REPLIES
Junior Contributor

Re: Minute Difference Between Time Datatypes

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

Enthusiast

Re: Minute Difference Between Time Datatypes

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?

Junior Contributor

Re: Minute Difference Between Time Datatypes

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

Enthusiast

Re: Minute Difference Between Time Datatypes

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.