Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    43
    Provided Answers: 1

    Answered: Format time difference as HH:MM:SS

    I capture CURRENT TIMESTAMP to TIMESTAMP variable vSTART when a particular procedure begins and another CURRENT TIMESTAMP to TIMESTAMP variable vFINISH just before the procedure generates its output. I then simply use vFINISH - vSTART to get the difference and assign this to one of the procedure's output variables so I can tell how long the procedure ran. But this returns an unformatted number. For example, if the difference in time is 1 hour, 35 minute, 42 seconds, the result is 13542.

    How can I get that number formatted as 1:34:42 or 01:34:42 so it is easier to read? Part of the issue is that I never know if the number will be more or less than an hour, so it could run for 47 minutes and 6 seconds, or 4706, which I would then need to have formatted as 00:47:06 or even just 47:06.

    I could live with minutes/seconds, so that 1:35:42 could be read as 95:42. I have been trying to use CAST, INSTR, and SUBSTR to convert it all to strings and then parse it out and concatenate in the colons, but it all gets enormously complex, and I figure there must be something simpler.
    Last edited by Brian.Hart; 02-11-17 at 16:10.

  2. Best Answer
    Posted by mark.bb

    "Try this:
    Code:
    values translate('AB:CD:EF', digits(dec(current timestamp - (current timestamp - 1 hour - 2 minute - 3 second), 6)), 'ABCDEF')
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    259
    Provided Answers: 39
    Try this:
    Code:
    values translate('AB:CD:EF', digits(dec(current timestamp - (current timestamp - 1 hour - 2 minute - 3 second), 6)), 'ABCDEF')
    Last edited by mark.bb; 02-12-17 at 04:42.
    Regards,
    Mark.

  4. #3
    Join Date
    Feb 2011
    Posts
    43
    Provided Answers: 1
    Thank you. That was, indeed, a lot simpler than all of my attempts to parse it all out! I was unaware of the translate function. That will become another tool in my DB2 arsenal.

Posting Permissions

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