postgresql 如何将“1 天 01:30:00”之类的间隔转换为“25:30:00”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/341384/
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
How to convert an interval like "1 day 01:30:00" into "25:30:00"?
提问by Ole
I need to add some intervals and use the result in Excel.
我需要添加一些间隔并在 Excel 中使用结果。
Since
自从
sum(time.endtime-time.starttime)
returns the interval as "1 day 01:30:00" and this format breaks my Excel sheet, I thought it'd be nice to have the output like "25:30:00" but found no way to do it in the PostgreSQL documentation.
将间隔返回为“1 天 01:30:00”，这种格式破坏了我的 Excel 工作表，我认为输出像“25:30:00”这样的输出会很好，但在 PostgreSQL 中找不到方法文档。
Can anyone here help me out?
这里有人能帮我吗？
采纳答案by neshkeev
Since there is not an exact solution for the topic:
由于该主题没有确切的解决方案：
=> SELECT date_part('epoch', INTERVAL '1 day 01:30:00') * INTERVAL '1 second' hours;
hours
-----------
25:30:00
(1 row)
Source: Documentation
来源：文档
回答by mat
The only thing I can come with (beside parsing the number of days and adding 24 to the hours every time) is :
我唯一能想到的（除了解析天数并每次将小时数增加 24 之外）是：
mat=> select date_part('epoch', '01 day 1:30:00'::interval);
date_part
-----------
91800
(1 row)
It will give you the number of seconds, which may be ok for excel.
它会给你秒数，这对于excel来说可能没问题。
回答by slim
You could use EXTRACTto convert the interval into seconds.
您可以使用EXTRACT将间隔转换为秒。
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
Then you would need to do your own maths (or let Excel do it).
然后你需要自己做数学（或让 Excel 来做）。
Note that '1 day' is not necessarily equivalent to '24 hours' - PostgreSQL handles things like an interval that spans a DST transition.
请注意，“1 天”不一定等同于“24 小时”——PostgreSQL 处理诸如跨越 DST 转换的间隔之类的事情。
回答by pilcrow
If you wanted postgres to handle the HH:MM:SS formatting for you, take the difference in epoch seconds and convert it to an interval scaled in seconds:
如果您希望 postgres 为您处理 HH:MM:SS 格式，请获取以纪元秒为单位的差异并将其转换为以秒为单位的间隔：
SELECT SUM(EXTRACT(EPOCH FROM time.endtime) - EXTRACT(EPOCH FROM time.starttime))
* INTERVAL '1 SECOND' AS hhmmss
回答by Jonathan Leffler
In standard SQL, you want to represent the type as INTERVAL HOUR TO SECOND, but you have a value of type INTERVAL DAY TO SECOND. Can you not use a CAST to get to your required result? In Informix, the notation would be either of:
在标准 SQL 中，您希望将类型表示为 INTERVAL HOUR TO SECOND，但您有一个 INTERVAL DAY TO SECOND 类型的值。您不能使用 CAST 来获得所需的结果吗？在 Informix 中，表示法是：
SUM(time.endtime - time.starttime)::INTERVAL HOUR(3) TO SECOND
CAST(SUM(time.endtime - time.starttime) AS INTERVAL HOUR(3) TO SECOND)
The former is, AFAIK, Informix-specific notation (or, at least, not standard); the latter is, I believe, SQL standard notation.
前者是 AFAIK，Informix 特定的表示法（或者，至少，不是标准的）；我相信后者是 SQL 标准表示法。
回答by dland
It can be done, but I believe that the only way is through the following monstrosity (assuming your time interval column name is "ti"):
可以做到，但我相信唯一的方法是通过以下怪物（假设您的时间间隔列名称为“ti”）：
select
to_char(floor(extract(epoch from ti)/3600),'FM00')
|| ':' || to_char(floor(cast(extract(epoch from ti) as integer) % 3600 / 60), 'FM00')
|| ':' || to_char(cast(extract(epoch from ti) as integer) % 60,'FM00')
as hourstamp
from whatever;
See? I told you it was horrible :)
看？我告诉过你这很可怕:)
It would have been nice to think that
要是这么想就好了
select to_char(ti,'HH24:MI:SS') as hourstamp from t
would worked, but alas, the HH24 format doesn't "absorb" the overflow beyond 24. The above comes (reconstructed from memory) from some code I once wrote. To avoid offending those of delicate constitution, I encapsulated the above shenanigans in a view...
会起作用，但可惜的是，HH24 格式不会“吸收”超过 24 的溢出。以上来自我曾经编写的一些代码（从内存中重建）。为避免触怒体质娇弱的人，我将上述恶作剧概括为一个观点……
