Geek Answers Handbook

Convert time in seconds to PostgreSQL

I have a time value of 04:30:25 that I want to convert to seconds. Is there a special feature for this?

I know that we can extract hours, minutes and seconds, and 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 a different way ...

+7
postgresql
source share
4 answers

Perhaps you can make this a function (just a quick setup, please review and change as necessary)?

 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');

Return:

 16225
+3
source share

You tried to use:

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

If this does not work, you can try the prefix of your time value '1970-01-01' and try:

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

Not tested, but it seems that these are your only options. Maybe.

+23
source share

You can skip an era or interval, i.e.

 SELECT EXTRACT(EPOCH FROM column ) from table
+8
source share

If you want to emulate the MySQL function time_to_sec , you can use this function:

 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';

The advantage is that it will work at PostgreSQL intervals (i.e. more than 24-hour periods), which will lead to a violation of the to_seconds function in the accepted answer.

0
source share

More articles:


All Articles