Convert time to seconds in PostgreSQL
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.
You may skip epoch or interval, ie:
SELECT EXTRACT(EPOCH FROM column ) from table
Perhaps you can make it a function (just a quick setup, please review and change as needed)?
CREATE OR REPLACE FUNCTION to_seconds(t text) RETURNS integer AS$BODY$ DECLARE hs INTEGER; ms INTEGER; s INTEGER;BEGIN SELECT (EXTRACT( HOUR FROM t::time) * 60*60) INTO hs; SELECT (EXTRACT (MINUTES FROM t::time) * 60) INTO ms; SELECT (EXTRACT (SECONDS from t::time)) INTO s; SELECT (hs + ms + s) INTO s; RETURN s;END;$BODY$ LANGUAGE 'plpgsql';Then just use it in your queries:
SELECT to_seconds('04:30:25');Returns:
16225