Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    8

    Unanswered: Convert seconds to hh:mm:ss

    How can I convert a seconds string into hh:mm:ss ( possibly to have more than 24 hours as well?) Any ideas? Can someone help out using a function?Thanks!!

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Can you give us some examples?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Feb 2014
    Location
    India
    Posts
    18
    Seems like there is a need to convert seconds into time format (HH:MMS)

    Following function can be used but it fails at 24hrs and returns 00:00:00
    db2 "select time('00:00:00') + 40 seconds from sysibm.sysdummy1"

    One workaround:

    db2 "select case when 240=86400 then '24:00:00' else char(time('00:00:00') + 240 seconds) end tmm from sysibm.sysdummy1"

    in this query, pass 240 as argument.

  4. #4
    Join Date
    Jan 2014
    Posts
    8
    when I execute following query

    HTML Code:
    select (nvl(round((time_end - time_start)*86400,2),round((sysdate - time_start)*86400,2))) as duration_seconds,
    (nvl(round((time_end - time_start)*1440,2),round((sysdate - time_start)*1440,2))) as duration_minutes,
    (nvl(round((time_end - time_start)*24,2),round((sysdate - time_start)*24,2))) as duration_hours,
    time_end - time_start
           
           from test.chrysalide_test_log
    order by 1 desc
    fetch first 4 rows only;
    I get the following result:

    DURATION SECONDS DURATION MINUTES DURATION HOURS TIME END - TIME START
    67954 1132.57 18.88 0.78650463
    6645 110.75 1.85 0.076909722
    5870 97.83 1.63 0.067939815
    5829 97.15 1.62 0.067465278

    When I substract time end from time start I get a result (number) in days.
    I would like convert this into HH:MMS.
    I have therefore recalculated the result in days into seconds (column A) but I would like to have the format hh:mm:ss...

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It might be more convenient to store the number of seconds as an integer instead of a string. In that case you should be able to get what you want by applying simple division and the MOD() function.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    When I substract time end from time start I get a result (number) in days.
    What were the data type of time_end and time_start?

    If they were TIME or TIMESTAMP,
    the result might be not in days.

    If they were DATE,
    the precision of the result might not include decimal.
    Last edited by tonkuma; 02-21-14 at 14:02.

Posting Permissions

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