Converting HH:MM:SS to seconds

hpaul · #1 (**permalink**) 07-26-11, 14:05

Hi all,

I was hoping to get some help with changing 'HH:MM

S' 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:MM

S is in varChar and I would like to output to be integer.

Thanks a ton for the help!

n_i · #2 (**permalink**) 07-26-11, 14:11

I guess you could use INSTR() and SUBSTR() to parse the string, then it's simple arithmetics.

MarkhamDBA · #3 (**permalink**) 07-26-11, 15:35

may be something like this:
SECOND(TIME('your string here')) will give you seconds;

or
MINUTE(TIME('your string here')) - will give you minutes.

hpaul · #4 (**permalink**) 07-26-11, 17:41

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

n_i · #5 (**permalink**) 07-26-11, 18:53

Quote:

Originally Posted by hpaul

figure out how to change it to an integer.

May be with the help of the INTEGER() function? Or a CAST expression?

stolze · #6 (**permalink**) 07-26-11, 19:46

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:MM

S format.

tonkuma · #7 (**permalink**) 07-26-11, 21:44

Quote:

... 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.

hpaul · #8 (**permalink**) 07-27-11, 10:00

Tonkuma ex. 1 worked perfectly for what I needed. Thanks a ton for the help, I appreciate it.

Thx everyone else too.

tonkuma · #9 (**permalink**) 07-27-11, 15:06

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech