Convert time to seconds in PostgreSQL

I have a time value 04:30:25 that I want to convert to seconds. Is there any de开发者_StackOverflowdicated 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...

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

If you want to emulate MySQL's time_to_sec function, you could use a function like this:

CREATE OR REPLACE FUNCTION time_to_sec(t text)
  RETURNS integer AS
$BODY$ 
DECLARE 
    s INTEGER;
BEGIN
    SELECT (EXTRACT (EPOCH FROM t::interval)) INTO s; 
    RETURN s;
END;
$BODY$
  LANGUAGE 'plpgsql';

It has the advantage that it will work with PostgreSQL intervals (ie: more than 24-hour periods), which would break the to_seconds function in the accepted answer.

As a simplified approach to @hdiogenes solution, just use this in the query:

SELECT EXTRACT (EPOCH FROM '04:30:25'::time)

from_seconds also to convert back

    CREATE OR REPLACE FUNCTION from_seconds(t integer)
        RETURNS time AS
    $BODY$ 
    DECLARE 
        h INTEGER;
        m INTEGER;
        s INTEGER;
        rv TIME;
    BEGIN
        SELECT t / 3600 INTO h; 
        SELECT t % 3600 / 60 INTO m;
        SELECT t % 60 INTO s;
        SELECT (h::text || ':' || m::text || ':' || s::text)::time INTO rv;
        RETURN rv;
    END;
    $BODY$
          LANGUAGE 'plpgsql';