在 PostgreSQL 中将时间转换为秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2816544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert time to seconds in PostgreSQL
提问by Pavunkumar
I have a time value 04:30:25 that I want to convert to seconds. Is there any dedicated function to do this?
我有一个时间值 04:30:25 我想转换为秒。有没有专门的功能来做到这一点？
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...
但我想要其他方式...
采纳答案by John P
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
回答by zaf
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:
如果这不起作用，您可以尝试使用“1970-01-01”作为时间值的前缀并尝试：
SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01 04:30:25');
Not tested but it seems these are your only options. Probably.
未经测试，但似乎这些是您唯一的选择。大概。
回答by Jor
You may skip epoch or interval, ie:
您可以跳过纪元或间隔，即：
SELECT EXTRACT(EPOCH FROM column ) from table
回答by hdiogenes
If you want to emulate MySQL's time_to_secfunction, you could use a function like this:
如果你想模拟 MySQL 的time_to_sec功能，你可以使用这样的函数：
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_secondsfunction in the accepted answer.
它的优点是它将与 PostgreSQL 间隔（即：超过 24 小时的时间段）一起使用，这会破坏to_seconds已接受答案中的函数。
回答by Igor Vagulin
from_seconds also to convert back
from_seconds 也转换回来
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';
