I want to turn strings that represent durations into their duration in seconds as an integer.

All of the strings are formatted in the following way:

hh:mm:ss

I was working with substrings, which I cast to integers and then multiplied with 3600, 60 or 1 respectively and in the end summed everything up.

Just like this:

SELECT cast(substr(time_string, 1, 2) as smallint) * 3600 cast(substr(time_string, 4, 2) as smallint) * 60 cast(substr(time_string, 7, 2) as smallint) as seconds from table_name

As I have to do this for multiple fields, I would be interested in a better way to achieve this result. My search for a better solution, though, fell flat so far.

I would appreciate any input or help!

CodePudding user response：

Try using date_parse, cast to time and use date_diff with required unit:

select date_diff(
        'second',
        time '00:00:00', -- zero time
        cast(date_parse('12:10:56', '%T') as time)
    ) seconds

Output: