Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Question Unanswered: Elapsed time between timestamps

    A query like this one:

    Code:
    SELECT TIMESTAMPA-TIMESTAMPB FROM MYTABLE
    Gives something like:
    2006-02-13 13:32:28.0 - 2005-11-09 15:05:36.0 =
    303222749

    which I know converts to:

    95 Days 22 Hours and 31 Min. 52 Secs.

    I know timestampdiff and other specific DB2 functions can handle this but the problem is that I'm using a report tool (iReport) that doesn't recognize DB specific functions.

    How I can do that from a query?

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    can you create new column in DB2 source database? If yes, then you can create formula with generated always statement.

    Sample:
    CREATE TABLE DB2ADMIN.TABLE (COL1 TIMESTAMP, COL2 TIMESTAMP)
    SET INTEGRITY FOR DB2ADMIN.TABLE OFF
    ALTER TABLE DB2ADMIN."TABLE" ADD COLUMN SUB1 INTEGER NOT NULL GENERATED ALWAYS AS (COL1-COL2)
    SET INTEGRITY FOR DB2ADMIN.TABLE IMMEDIATE CHECKED FORCE GENERATED

    Note: You have to turn off integrity check before alter table definition is applied, just like in sample above.

    If above statement is not a solution, try to create exactly the input you need with formula.

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Sadly adding a column is not possible for 2 reasons, I don't have admin access and is a validated enviroment.

    I'm not clear with what you mean by:

    "...try to create exactly the input you need with formula."

    I'm not really used to DB2. I get this from a Crystal Report's report. Using a viewer it defines this field as @Elapsed time which get the results I showed.

    Here's what I've managed to do (from http://www-128.ibm.com/developerwork...211yip3.html):

    Code:
    CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) 
    RETURNS INT 
    RETURN ( 
    (DAYS(t1) - DAYS(t2)) * 86400 + 
    (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) 
    )
    And my new query:
    Code:
    SELECT secondsdiff(A.DEPT_TIMESTAMP,B.DEPT_TIMESTAMP)/86400 as elapsed_days, 
    (secondsdiff(A.DEPT_TIMESTAMP,B.DEPT_TIMESTAMP)-((secondsdiff(A.DEPT_TIMESTAMP,B.DEPT_TIMESTAMP)/86400)*86400))/3600 as elapsed_hours
     FROM BAX_DEPT_SCHED_LG A,BAX_DEPT_SCHED_LG B WHERE 
    A.BATCH_ID = B.BATCH_ID AND B.REASON = '' 
    AND A.REASON ='MFG VERIFICATION' AND B.BATCH_ID ='UR295881'
    And the result:

    Code:
    ELAPSED_DAYS	ELAPSED_HOURS
    95	             22
    I went for the mins/sec part but the query became way too complicated and never pulled it of.

    Maybe someone out there has a better way or can pull it off in a more elegant way.

    Thanks in advance.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by javydreamercsw
    2006-02-13 13:32:28.0 - 2005-11-09 15:05:36.0 =
    303222749
    which I know converts to:
    95 Days 22 Hours and 31 Min. 52 Secs.
    Actually, it converts to 3 months, 3 days, 22 hours, 27 minutes, 49 seconds.

    Hence you may interpret a timestamp difference with the SUBSTR function to extract the different parts.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Thumbs up

    Here's the answer:

    Code:
    SELECT
    SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),1,2) as elapsed_centuries,
    SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),3,2) as elapsed_years,
    SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),5,2) as elapsed_months,
    SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),7,2) as elapsed_days,
     SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),9,2) as elapsed_hours,
    SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),11,2) as elapsed_mins,
    SUBSTR(CHAR(A.DEPT_TIMESTAMP - B.DEPT_TIMESTAMP),13,2) as elapsed_seconds
    FROM BAX_DEPT_SCHED_LG A,BAX_DEPT_SCHED_LG B WHERE
    A.BATCH_ID = B.BATCH_ID AND B.REASON = '' 
    AND A.REASON ='MFG VERIFICATION' AND B.BATCH_ID ='UR295881'
    And the result:

    Code:
    ELAPSED_CENTURIES	ELAPSED_YEARS	ELAPSED_MONTHS	ELAPSED_DAYS	ELAPSED_HOURS	ELAPSED_MINS	ELAPSED_SECONDS
    00	00	03	03	22	26	52
    I'm guessing the first two fields are centuries and years. Please correct me if I'm wrong.

    Thanks for the usefull hint!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •