postgresql 如何使用 postgres 将间隔转换为小时数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/952493/
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 do I convert an interval into a number of hours with postgres?
提问by agnul
Say I have an interval like
说我有一个像
4 days 10:00:00
in postgres. How do I convert that to a number of hours (106 in this case?) Is there a function or should I bite the bullet and do something like
在 postgres 中。我如何将其转换为小时数（在这种情况下为 106？）是否有功能，或者我应该硬着头皮做类似的事情
extract(days, my_interval) * 24 + extract(hours, my_interval)
回答by Magnus Hagander
Probably the easiest way is:
可能最简单的方法是：
SELECT EXTRACT(epoch FROM my_interval)/3600
回答by Pujan Srivastava
If you want integer i.e. number of days:
如果你想要整数，即天数：
SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int
回答by belveryin
To get the number of days the easiest way would be:
要获得天数，最简单的方法是：
SELECT EXTRACT(DAY FROM NOW() - '2014-08-02 08:10:56');
As far as I know it would return the same as:
据我所知，它会返回与以下内容相同的内容：
SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int;
回答by Janek Olszak
If you convert table field:
如果转换表字段：
Define the field so it contains seconds:
CREATE TABLE IF NOT EXISTS test ( ... field INTERVAL SECOND(0) );Extract the value. Remember to cast to intother wise you can get an unpleasant surprise once the intervals are big:
EXTRACT(EPOCH FROM field)::int
定义字段，使其包含秒：
CREATE TABLE IF NOT EXISTS test ( ... field INTERVAL SECOND(0) );提取值。记住要强制转换为 int否则一旦间隔很大你会得到一个不愉快的惊喜：
EXTRACT(EPOCH FROM field)::int
回答by haoming
select floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600)), count(*)
from od_a_week
group by floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600));
The ::intconversion follows the principle of rounding.
If you want a different result such as rounding down, you can use the corresponding math function such as floor.
该::int转换遵循四舍五入的原则。如果您想要不同的结果，例如四舍五入，您可以使用相应的数学函数，例如floor.
回答by Arunkumar Papena
select date 'now()' - date '1955-12-15';
Here is the simple query which calculates total no of days.
这是计算总天数的简单查询。
