ARG!! Displaying diffs b/w timestamps 4 UDB

Rediranch · #1 (**permalink**) 07-25-09, 10:24

This thing is driving me nuts. Why can't IBM just make it simple (ok, that was a silly question).

I've been googling and searching IBM to figure out how to display, in a timestamp or date/time format, the difference between two timestamps.

I've discovered timestampDiff built in function, but it returns only a portion of the date, based on what parm you send it - so seconds, minutes, hours, etc.

That's great! But how do you then covert that to a readable date/time/timestamp????

I'm trying to look at the difference b/w two timestamps that at most are going to be 24 hrs apart from each other.

If I've got two timestamps where the diff is 01:30:25 (HH:MM: SS) (no extra space in display, but in forum a smiley appears w/o it), lets say, then if I return seconds from the timestampdiff function I'll get 5425 back - now what do I do with that to show 01:30:25???

timestampdiff = DB2 Universal Database

Marcus_A · #2 (**permalink**) 07-25-09, 13:33

You can do some math to convert the format.

The problem is that a date, time, or timestamp format must contain a valid date, time, or timestamp which is context sensitive to the actual date or time. So if the difference is more than 25 hours, then what happens? DB2 will not accept an hour value of 25 in a time format because it is invalid.

If you want DB2 to convert 25 hours to 1 day + 1 hour there is no format that has just days and hours without the year. Then there is the problem when you hit more than the number of days in a month, since not every month has the same number of valid days.

Stealth_DBA · #3 (**permalink**) 07-25-09, 21:33

Rediranch, here is something that may (or may not) interest you. This SQL will take a timestamp duration and display it in a timestamp like format (repeat, this is NOT a timestamp. It is just a character representation of the timestamp duration similar to a timestamp format).

Code:

WITH TS_TAB
  AS(
     SELECT '000'      || CAST(YEAR       (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(4))   AS YYYY
          ,  LENGTH(STRIP(CAST(YEAR       (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(4)))) AS YYYY_LEN
          , '0'        || CAST(MONTH      (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2))   AS MM
          ,  LENGTH(STRIP(CAST(MONTH      (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2)))) AS MM_LEN
          , '0'        || CAST(DAY        (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2))   AS DD
          ,  LENGTH(STRIP(CAST(DAY        (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2)))) AS DD_LEN
          , '0'        || CAST(HOUR       (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2))   AS HH
          ,  LENGTH(STRIP(CAST(HOUR       (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2)))) AS HH_LEN
          , '0'        || CAST(MINUTE     (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2))   AS MN
          ,  LENGTH(STRIP(CAST(MINUTE     (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2)))) AS MN_LEN
          , '0'        || CAST(SECOND     (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2))   AS SS
          ,  LENGTH(STRIP(CAST(SECOND     (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(2)))) AS SS_LEN
          , '00000'    || CAST(MICROSECOND(CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(6))   AS MS
          ,  LENGTH(STRIP(CAST(MICROSECOND(CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 HOUR - 3 MINUTE - 2 SECOND - 1 MICROSECOND)) AS CHAR(6)))) AS MS_LEN
     FROM SYSIBM.SYSDUMMY1
   )
SELECT SUBSTR(YYYY, YYYY_LEN, 4) || '-'
    || SUBSTR(MM  , MM_LEN  , 2) || '-'
    || SUBSTR(DD  , DD_LEN  , 2) || '-'
    || SUBSTR(HH  , HH_LEN  , 2) || '.'
    || SUBSTR(MN  , MN_LEN  , 2) || '.'
    || SUBSTR(SS  , SS_LEN  , 2) || '.'
    || SUBSTR(MS  , MS_LEN  , 6) AS TS_DUR_DISPLAY 
FROM TS_TAB

TS_DUR_DISPLAY            
--------------------------
0000-00-00-04.03.02.000001

I am using CURRENT_TIMESTAMP and subtracting durations to get a different timestamp for the substraction (otherwise, I would get all zeros in the output). You would put your second timestamp in place of this.

You could do this all in one very large statement and not use the Common Table Expression (CTE). I did this to make the process a little easier to see.

You could also put this (or something like it) in a function.

Basically, it subtracts the timestamps to get the timestamp duration.
Then it uses the various date/time functions to extract them out (the Year, Month, Day, etc.).
Since these results are Integers, I Cast them as Characters.
I also Concatenate enough leading zeros to ensure they are at least the length of the value.
I also run the same process (except for concatenating the zeros) with a STRIP function (to remove spaces) and the LEN function to get the actual lenght of the value (Year could be 1-4 characters, Microseconds could be 1-6 characters and all the rest could be 1-2 characters).

This gets the base values in the CTE TS_TAB.

Then it is a simple matter of using the SUBSTR function (with the Length value to get the correct starting position) and concatenate all the values together along with separators.

This results in a character string that represents the duration in a timestamp-like format.

tonkuma · #4 (**permalink**) 07-25-09, 23:30

Sample data and expressions to format it could be simplified.
(There would be more other ways to format timestamp duration.)

Code:

------------------------------ Commands Entered ------------------------------
WITH TS_TAB(ts_duration) AS (
VALUES
 CURRENT_TIMESTAMP - 
(CURRENT_TIMESTAMP - 7 YEAR - 6 MONTH - 5 DAY - 4 HOUR - 3 MINUTE - 2 SECOND - 135789 MICROSECOND)
)
SELECT ts_duration
     , TRANSLATE( 'yYzZ-mM-dD-hH.nN.sS.abcefg'
                , DIGITS(ts_duration)
                , 'yYzZmMdDhHnNsSabcefg'
                ) AS ts_dur_display_timestamp
     , TRANSLATE( 'mM/dD/yYzZ hH:nN:sS.abcefg'
                , DIGITS(ts_duration)
                , 'yYzZmMdDhHnNsSabcefg'
                ) AS ts_dur_display_date_time
  FROM TS_TAB
;
------------------------------------------------------------------------------

TS_DURATION            TS_DUR_DISPLAY_TIMESTAMP   TS_DUR_DISPLAY_DATE_TIME  
---------------------- -------------------------- --------------------------
    70605040302.135789 0007-06-05-04.03.02.135789 06/05/0007 04:03:02.135789

  1 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech