psql : Convert the time to seconds

Question

Hi all.

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

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

SELECT  extract ( hour  from  t ) * 60*60 + extract (  minutes from t ) * 60 + extract  ( seconds from t )  from  test ; 

But I want some other way...

Thanks

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.

Answer 2

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