PostgreSQL：将间隔格式化为分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3460805/
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
PostgreSQL: format interval as minutes
提问by Konrad Garus
When I subtract timestamps, the interval is in form DD:HH:MM:SS. How can I convert it all to minutes without extracting days and hours and multiplication/addition? I'm looking for a single function which I can substitute for date_partin this query so that it returns 65:
当我减去时间戳时，间隔的格式为 DD:HH:MM:SS。如何在不提取天数和小时数以及乘法/加法的情况下将其全部转换为分钟？我正在寻找一个可以date_part在此查询中替换的函数，以便它返回 65：
select date_part('minutes', '65 minutes'::interval);
Context: I need to know how many minutes have passed since given timestamp.
上下文：我需要知道自给定时间戳以来已经过去了多少分钟。
回答by Milen A. Radev
SELECT EXTRACT(EPOCH FROM '2 months 3 days 12 hours 65 minutes'::INTERVAL)/60;
seems to work.
似乎工作。
WARNING: "seems" is the key word.
警告：“似乎”是关键词。
回答by IMSoP
As a previous answer points out, the trick is to convert the interval to an "epoch", that is, an absolute number of seconds, and then divide appropriately for absolute numbers of other units.
正如前面的答案所指出的，诀窍是将间隔转换为“纪元”，即绝对秒数，然后适当划分其他单位的绝对数。
Sone years ago, I wrote an interval_convertfunctionwhich generalises this to most of the arguments accepted by date_part, assuming that a month is 30 days, and a year is 365.25 days.
几年前，我编写了一个interval_convert函数，将其推广到 接受的大多数参数date_part，假设一个月是 30 天，一年是 365.25 天。
Feel free to use and modify as you see fit:
随意使用和修改你认为合适的：
CREATE FUNCTION
interval_convert(in_unit text, in_interval interval)
RETURNS double precision
AS $FUNC$
SELECT
EXTRACT(
EPOCH FROM
-- in_interval
)
/
-- Slightly lazy way of allowing both singular and plural
-- has side effect that 'centurie' and 'centurys' are accepted
-- but otherwise behaves similarly to DATE_TRUNC
CASE TRIM(TRAILING 's' FROM LOWER(
-- in_unit
))
WHEN 'microsecond' THEN 0.000001
WHEN 'millisecond' THEN 0.001
WHEN 'second' THEN 1
WHEN 'minute' THEN 60
WHEN 'hour' THEN 3600
WHEN 'day' THEN 86400
WHEN 'week' THEN 604800
WHEN 'month' THEN 2592000 -- 30 days
-- WHEN 'quarter' THEN -- Not supported
WHEN 'year' THEN 31557600 -- 365.35 days
WHEN 'decade' THEN 315576000
WHEN 'century' THEN 3155760000
WHEN 'centurie' THEN 3155760000
WHEN 'millennium' THEN 31557600000
WHEN 'millennia' THEN 31557600000
END
$FUNC$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT;
回答by Joshua D. Drake
I don't actually think you can without doing a bunch of weirdness (like to_char) because minutes roll over at 59. Which is why you get:
我实际上不认为你可以不做一堆奇怪的事情（比如 to_char），因为分钟数会在 59 处滚动。这就是为什么你得到：
postgres=# select date_part('minutes', '65 minutes'::interval);
date_part
-----------
5
(1 row)
postgres=# select '65 minutes'::interval
postgres-# ;
interval
----------
01:05:00
(1 row)
