在PostgreSQL中一天多于24小时

Question

假设我有这个架构：

Assuming I have this schema:

假设我有这个模式:

Assuming I have this schema:

create table rental (
    id           integer,
    rental_date  timestamp,
    customer_id  smallint,
    return_date  timestamp,
);

运行这个查询返回了奇怪的结果：

select customer_id, avg(return_date - rental_date) as "avg"
from rental
group by customer_id
order by "avg" DESC

它显示：

customer_id|avg_rent_duration     |
-----------|----------------------|
        315|     6 days 14:13:22.5|
        187|5 days 34:58:38.571428|
        321|5 days 32:56:32.727273|
        539|5 days 31:39:57.272727|
        436|       5 days 31:09:46|
        532|5 days 30:59:34.838709|
        427|       5 days 29:27:05|
        555|5 days 26:48:35.294118|
...

599 rows

为什么会有像5天34:58:38、5天32:56:32等这样的数值？我以为一天只有24小时，也许我错了。

编辑

演示在这里：http://sqlfiddle.com/#!17/caa7a/1/0

样本数据：

insert into rental (rental_date, customer_id, return_date)
values
('2007-01-02 13:10:06', 1, '2007-01-03 01:01:01'),
('2007-01-02 01:01:01', 1, '2007-01-09 15:10:06'),
('2007-01-10 22:10:06', 1, '2007-01-11 01:01:01'),
('2007-01-30 01:01:01', 1, '2007-02-03 22:10:06');

- rap-2-h

---

你可以使用 justify_interval() 函数来规范化区间。 - user330315

@a_horse_with_no_name 谢谢，看起来可行！你能否详细解释一下（如何/为什么），并将您的评论发布为答案？ - rap-2-h

@LaurenzAlbe 问题不在于差异不是整数天。而是小时数经常超过24小时，比如“5天34:58:38”，问题是为什么不是“6天10:58:38”。 - elixenide

我强烈怀疑这与使用“avg”有关；减法可能是正确的，但“avg”会导致问题。然而，我并不确定。 - elixenide

哦，我明白了。谢谢。 - Laurenz Albe

Answer 1

您需要使用 justify_interval() 函数来调整间隔：

select customer_id, justify_interval(avg(return_date - rental_date)) as "avg"
from rental
group by customer_id
order by "avg" DESC;

请参考官方文档：

使用justify_days和justify_hours来调整时间间隔，同时进行额外的符号调整。

然而，没有解释为什么在没有使用justify_interval()的情况下操作结果如此奇怪（换句话说，为什么我们必须应用这个函数）。

注意：感谢@a_horse_with_no_name提供的评论。

- rap-2-h

Answer 2

这是解释行为的尝试。

在PostgreSQL中，区间运算期间，区间不会被“调整”超过必要的程度。我认为有两个原因：

• 速度
• 精确度损失（例如，将天数转换为月份时，假设一个月有30天）

因此，您会得到像这样的结果：

SELECT INTERVAL '1 day 20 hours' + INTERVAL '5 days 30 hours';

    ?column?     
-----------------
 6 days 50:00:00
(1 row)

同样适用于除法：

SELECT INTERVAL '6 days 50 hours' / 2;

    ?column?     
-----------------
 3 days 25:00:00
(1 row)

始终将时间定为小于24小时，像你正在计算的avg这样的长时间计算会使计算变得不必要复杂，而且正如你发现的那样，有函数可以调整结果。

- Laurenz Albe