How to find time difference in mins from two timestamp variables in UDB

In one of my UDB stopred procedure, I am extracting 2 fields (of datatype  timestamp) from table record_log ( through cursor) those are "timestamp_create" & "timestamp_update". I need to calculate the difference in between these 2 timestamp variables in minutes & store it in another variable. Can anybody help me to do same.
Amit_PekamwarAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Amit,

Use the TIMESTAMPDIFF function.  It will compare two timestamps and return the difference in the requested interval.

  http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000861.htm


SELECT TIMESTAMPDIFF (4, timestamp1, timestamp2) FROM ....



Good Luck,
Kent
0
 
cmaslenCommented:
DB2 saves differences between timestamps in what they describe as a duration, but it's really a decimal(20, 6) where the digits represent the constituent fields in a timestamp:

yyyymmddhhmmss.nnnnnn

More info on this can be found at:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0023457.htm

Fortunately you can apply functions to the decimal to extract the parts of the duration you're interested in. So you can write a UDF to get the difference based on those components. The larger the time difference the more complex the calc becomes. In the code snippet I've gone to timestamps within the same month.

select  a as ts_1,
        b as ts_2,
        ( 1440 * day( a - b ) ) + ( 60 * hour( a - b ) ) + minute( a - b ) as diff_in_mins_within_a_month
from (values( current_timestamp, (current_timestamp - 1 day) - 75 minutes ) ) as t(a, b);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.