Solved

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

Posted on 2009-04-26
636 Views
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
Question by:Amit_Pekamwar

LVL 45

Accepted Solution

Hi Amit,

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

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

Good Luck,
Kent
0

LVL 1

Assisted Solution

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

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);
``````
0

## Featured Post

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…