Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    27

    Unanswered: how to SUBTRACT two days in format days:hours:minutes???

    Hi,

    I read a lot about how to subtract days in DB2 but I could't find how to subtract them in result format days:hours:minutes?
    Can someone help me?

    Example: 4.April.2011 15h:46min - 2.April.2011 13h:20
    Result: 2:2:26
    Thanks for help

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by the fine manual
    The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and fractional seconds between the two timestamps. The data type of the result is DECIMAL(14+s,s), where s is the maximum timestamp precision of TS1 and TS2.
    More of the same: Datetime operations and durations - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    As far as I know, there is no function returns formatted result for subtracted days.

    Please see DB2 Basics: Fun with Dates and Times

    You need format by yourself.

    Here is an example.
    Note: Result column "FORMAT_0" may be not accurate when two arguments of subtract are far apart.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT to_ts , from_ts
         , to_ts - from_ts AS subtract_ts
         , TRANSLATE('ab:cd:ef' , DIGITS(to_ts - from_ts) , '......abcdef') AS format_0
         , SUBSTR(
              DIGITS(
                 DAYS(to_ts) - DAYS(from_ts)
                 - CASE WHEN TIME(to_ts) < TIME(from_ts) THEN 1 ELSE 0 END
              )
            , 9 , 2
           )
           || TRANSLATE(':cd:ef' , DIGITS(to_ts - from_ts) , '......abcdef')
           AS exact_fmt
     FROM  (VALUES
             (  TO_DATE('4.April.2011 15:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('2.April.2011 13:20'     , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.March.2011 15:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('31.January.2011 13:20'  , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.April.2011 15:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('28.February.2011 13:20' , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.April.2011 12:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('28.February.2011 13:20' , 'dd.month.yyyy hh24:mi')
             )
          ) p(to_ts , from_ts)
    ;
    ------------------------------------------------------------------------------
    
    TO_TS                      FROM_TS                    SUBTRACT_TS            FORMAT_0 EXACT_FMT
    -------------------------- -------------------------- ---------------------- -------- ---------
    2011-04-04-15.46.00.000000 2011-04-02-13.20.00.000000         2022600.000000 02:02:26 02:02:26 
    2011-03-01-15.46.00.000000 2011-01-31-13.20.00.000000       101022600.000000 01:02:26 29:02:26 
    2011-04-01-15.46.00.000000 2011-02-28-13.20.00.000000       101022600.000000 01:02:26 32:02:26 
    2011-04-01-12.46.00.000000 2011-02-28-13.20.00.000000       100232600.000000 00:23:26 31:23:26 
    
      4 record(s) selected.
    Last edited by tonkuma; 05-05-11 at 13:49. Reason: Change MIDNIGHT_SECONDS to TIME in the example.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    exact_fmt was made a little shorter:

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT to_ts , from_ts
         , to_ts - from_ts AS subtract_ts
         , TRANSLATE(  'ab:cd:ef'
                     , DIGITS(to_ts - from_ts)
                     , '......abcdef'
                    ) AS format_0
         , TRANSLATE(  'ab:cd:ef'
                     , DIGITS(  DAYS(to_ts - MIDNIGHT_SECONDS(from_ts) SECONDS)
                              - DAYS(from_ts) )
                       ||
                       DIGITS(to_ts - from_ts)
                     , '........ab........cdef'
                    ) AS exact_fmt
     FROM  (VALUES
             (  TO_DATE('4.April.2011 15:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('2.April.2011 13:20'     , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.March.2011 15:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('31.January.2011 13:20'  , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.April.2011 15:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('28.February.2011 13:20' , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.April.2011 12:46'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('28.February.2011 13:20' , 'dd.month.yyyy hh24:mi')
             )
           , (  TO_DATE('1.April.2011 13:20'     , 'dd.month.yyyy hh24:mi')
              , TO_DATE('28.February.2011 13:20' , 'dd.month.yyyy hh24:mi')
             )
          ) p(to_ts , from_ts)
    ;
    ------------------------------------------------------------------------------
    
    TO_TS                      FROM_TS                    SUBTRACT_TS            FORMAT_0 EXACT_FMT
    -------------------------- -------------------------- ---------------------- -------- ---------
    2011-04-04-15.46.00.000000 2011-04-02-13.20.00.000000         2022600.000000 02:02:26 02:02:26 
    2011-03-01-15.46.00.000000 2011-01-31-13.20.00.000000       101022600.000000 01:02:26 29:02:26 
    2011-04-01-15.46.00.000000 2011-02-28-13.20.00.000000       101022600.000000 01:02:26 32:02:26 
    2011-04-01-12.46.00.000000 2011-02-28-13.20.00.000000       100232600.000000 00:23:26 31:23:26 
    2011-04-01-13.20.00.000000 2011-02-28-13.20.00.000000       101000000.000000 01:00:00 32:00:00 
    
      5 record(s) selected.
    Last edited by tonkuma; 05-07-11 at 03:50. Reason: Replace the expression for exact_fmt.

Posting Permissions

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