I'm currently looking at some data that has the uptime for pieces of equipment, its a CHARACTER VARYING field on the table and the data looks like this -
11 hours, 02:33.00
3 days, 09:37:20.37
17 days, 16:05:32.00
58 minutes, 02.00
127 days, 16:05:32.00
This is a pain. My suggestion is
case and date/time logic. I am not very familiar with Netezza-specific functions for date/times, but the logic is like this:
select (case when val like '% hours%' then cast(substr(val, instr(val, ' ')) as int) * 60 * 60 + -- extract the seconds from substr(val, -8, 8) . . .
That is, get the first number out of the string and multiply it by the appropriate amount to get seconds. Use
case to separate out each of the possible formats for the string.
Then look for the "time" component at the end and translate that into seconds. I don't know off-hand how to do that in Netezza.