Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: Converting HH:MM:SS to seconds

    Hi all,

    I was hoping to get some help with changing 'HH:MMS' to secs or minutes within my SQL code for a query. I've looked around for more then an hour online with no luck, so I hoping you guys can help.

    As a reference, I'm using DB2 and it is possible for the hour to be 3 digits or more (ex. 206:23:19). Also HH:MMS is in varChar and I would like to output to be integer.

    Thanks a ton for the help!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess you could use INSTR() and SUBSTR() to parse the string, then it's simple arithmetics.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    may be something like this:
    SECOND(TIME('your string here')) will give you seconds;

    or
    MINUTE(TIME('your string here')) - will give you minutes.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jul 2011
    Posts
    3
    Thanks for the suggestions, however I still haven't been able to get it to work.

    I looked into the substr. but I wasn't having luck since it converts to varchar and I can't then figure out how to change it to an integer. (I still need to later on get a sum of the time, since I have multiple records that are all related).
    Although with some more help, it might work?


    SECOND(TIME('your string here')) doesn't work for me either since I get a error 180 which is basically that the format doesn't conform to a valid format. I think it has to do with the ':' in the middle

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hpaul View Post
    figure out how to change it to an integer.
    May be with the help of the INTEGER() function? Or a CAST expression?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would do something like this to avoid the parsing:
    Code:
    VALUES HOUR('01:45:30') * 60 * 60 + MINUTE('01:45:30') * 60 + SECOND('01:45:30')
    If you get SQL0180, it would be good to know what your time values look like. Because DB2 can easily parse HH:MMS format.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... I'm using DB2 and it is possible for the hour to be 3 digits or more (ex. 206:23:19).
    If considered the additional requirement, HOUR, MINUTE , SECOND functions may get error,
    and more complex expressions may be necessary.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT string_time
         , INTEGER( '0' || SUBSTR(string_time , 1 , POSSTR(string_time , ':') - 2) ) * 36000
           + MIDNIGHT_SECONDS( SUBSTR(string_time , POSSTR(string_time , ':') - 1) ) AS seconds_time
     FROM  (VALUES '206:23:19'
                 ,  '13:57:09'
                 ,   '1:06:05'
                 ,   '0:00:00'
                 ,  '72:06:05'
                 , '123:46:06' ) s(string_time)
    ;
    ------------------------------------------------------------------------------
    
    STRING_TIME SECONDS_TIME
    ----------- ------------
    206:23:19         742999
    13:57:09           50229
    1:06:05             3965
    0:00:00                0
    72:06:05          259565
    123:46:06         445566
    
      6 record(s) selected.
    Example 2:
    Code:
    SELECT string_time
         , INTEGER( SUBSTR(string_time , 1              , first_sep - 1) ) * 3600
         + INTEGER( SUBSTR(string_time , first_sep  + 1 , second_sep - first_sep - 1) ) * 60
         + INTEGER( SUBSTR(string_time , second_sep + 1) ) AS seconds_time
     FROM  (VALUES '206:23:19'
                 ,  '13:57:09'
                 ,   '1:06:05'
                 ,   '0:00:00'
                 ,  '72:06:05'
                 , '123:46:06'
           ) s(string_time)
     CROSS JOIN
           LATERAL
           (VALUES ( POSSTR(string_time , ':') , INSTR(string_time , ':' , 1 , 2) )
           ) sep(first_sep , second_sep)
    ;
    ------------------------------------------------------------------------------
    
    STRING_TIME SECONDS_TIME
    ----------- ------------
    206:23:19         742999
    13:57:09           50229
    1:06:05             3965
    0:00:00                0
    72:06:05          259565
    123:46:06         445566
    
      6 record(s) selected.
    Last edited by tonkuma; 07-26-11 at 23:13. Reason: Add data '206:23:19'

  8. #8
    Join Date
    Jul 2011
    Posts
    3
    Tonkuma ex. 1 worked perfectly for what I needed. Thanks a ton for the help, I appreciate it.

    Thx everyone else too.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This may be slightly better than Example 1.

    The reasons:
    1) Schema of LEFT function was SYSIBM later than DB2 9.5.
    So, the performance got same level as SUBSTR.
    And, length of expression using LEFT is shorter than using SUBSTR in the examples.
    2) Generally speaking, integer operations show better performance than string concatenation operations.

    Example 1a:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT string_time
         , INTEGER( LEFT(string_time , POSSTR(string_time , ':') - 1) ) / 10 * 36000
           + MIDNIGHT_SECONDS( SUBSTR(string_time , POSSTR(string_time , ':') - 1) ) AS seconds_time
     FROM  (VALUES '206:23:19'
                 ,  '13:57:09'
                 ,   '1:06:05'
                 ,   '0:00:00'
                 ,  '72:06:05'
                 , '123:46:06' ) s(string_time)
    ;
    ------------------------------------------------------------------------------
    
    STRING_TIME SECONDS_TIME
    ----------- ------------
    206:23:19         742999
    13:57:09           50229
    1:06:05             3965
    0:00:00                0
    72:06:05          259565
    123:46:06         445566
    
      6 record(s) selected.

Posting Permissions

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