在PostgreSQL中将时间转换为秒 [英] Convert time to seconds in PostgreSQL

问题描述

我有一个要转换为秒的时间值04:30:25。
是否有专用的功能来执行此操作？

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

推荐答案

也许您可以使其功能（只是快速设置，请根据需要进行检查和更改）？

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

返回值：

16225

这篇关于在PostgreSQL中将时间转换为秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！