Convert hh:mm to ss on netezza (SQL)

Question

I have a char datatype that contains data in hh:mm format and i would like to convert into seconds in netezza DB.

For eg: 1:23 to seconds?

Thanks

Answer 1

First you need to convert your time to the right datatype and then extract EPOCH to get the seconds.

select
extract ( EPOCH from to_timestamp(to_char(current_date,'YYYY-MM-DD')
||' '||sample.dt_str,'YYYY-MM-DD MI:SS')-current_date)
from
(
        select '01:23' as dt_str
) sample

Also if you have the SQL Functions Toolkit installed you could simply run this.

select sql_functions.admin.second ('01:01:23')

Answer 2

select '1:23' aTime, position(':' in aTime) pos, 
    cast(substring(aTime from 1 for (pos-1)) as int)*3600 HR, 
    cast(substring(aTime from (pos+1)) as int) * 60 mins, 
    hr+mins secs