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

Posted on 2009-04-26
Last Modified: 2013-12-26
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.
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,
    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:


    More info on this can be found at:

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    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…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now