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...

Solution 1

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

Solution 2

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.

Solution 3

You may skip epoch or interval, ie:

SELECT EXTRACT(EPOCH FROM column ) from table

Solution 4

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.

Solution 5

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

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

View more solutions

Comments

[Josh]

I used FLOOR(EXTRACT(EPOCH FROM column)) to round down and get an integer

[leonardkraemer]

This doesnt't answer the question, but the reversal of the question.

[Czyzby]

I believe this might break if intervals are longer than 24 hours.