Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 716
  • Last Modified:

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.
0
Amit_Pekamwar
Asked:
Amit_Pekamwar
2 Solutions
 
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now