Convert time to seconds in PostgreSQL

Question

I have a time value 04:30:25 that I want to convert to seconds. Is there any dedicated function to do this?

I know that we can extract hours, minutes and seconds, then calculate the seconds.

SELECT EXTRACT(hour FROM t)*60*60        + EXTRACT(minutes FROM t)*60        + EXTRACT(seconds FROM t)   FROM test;  

But I want some other way...

Answer 1

Have you tried using:

SELECT EXTRACT(EPOCH FROM INTERVAL '04:30:25'); 

If that doesn't work you could try to prefix your time value with '1970-01-01' and try:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01 04:30:25'); 

Not tested but it seems these are your only options. Probably.