Marc Voller Marc Voller - 1 month ago 10
SQL Question

Netezza - Extract time in seconds from varying varchar field containing text and numbers

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


As you can see above it's always the highest value at the time the data is pulled that's in text but it'll never count higher than number of days, it doesn't go into months ie
127 days, 16:05:32.00


I need to extract the total time in seconds for each row, I've been thinking a
CASE WHEN
statement to look for the text first, "hours" etc, extract the first numerical value convert that to seconds, then convert the remaining numerical values to seconds and add together.

Not looking for a total solution of course, just some advice on what might work.

Edit : Need to do this without altering the table if possible.

Answer

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.