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

cankovicv · #1 (**permalink**) 05-05-11, 02:51

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

n_i · #2 (**permalink**) 05-05-11, 07:30

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

tonkuma · #3 (**permalink**) 05-05-11, 07:39

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.

tonkuma · #4 (**permalink**) 05-06-11, 17:36

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech