为什么 postgres 对相同的间隔值显示两种不同的格式？

我正在帮忙做这件事question尝试更改间隔的格式。

from '01 day 22:10:37'  to  '46:10:37'

我给出了一个字符串操作的解决方案。但后来我发现 postgres 可以在两种不同的格式上显示相同的间隔。

SELECT '2016-01-27 08:51:02'::timestamp - '2016-01-25 10:40:25'::timestamp end_date,
       '46:10:37'::interval interval_date;

有趣的事情。有一个function进行逆过程

 justify_hours('46:10:37'::interval) --> '1 day 22:10:37'

所以我想知道是否有直接的方法来解决这个问题。为什么相同的区间值有两个不同的结果。

pgAdmin 输出：

当一个区间为两个时间戳之间的差异它总是以小时为单位（即它有standard格式）。例子：

select
    '2015-01-01 13:0:0'::timestamp - '2014-01-01 23:0:0'::timestamp, --> 364 days 14:00:00
    '2015-01-01 13:0:0'::timestamp - '2014-01-01 03:0:0'::timestamp, --> 365 days 10:00:00
    '2015-01-01 13:0:0'::timestamp - '2015-01-01 03:0:0'::timestamp; --> 10:00:00

间隔计算分别对日期部分和时间部分执行，因此它们可能会导致奇怪的格式。例子：

select 
    '2 day 1:00:00'::interval- '1 day 2:00:00'::interval,    --> 1 day -01:00:00 (!!)
    '2 day 100:00:00'::interval+ '1 day 60:00:00'::interval, --> 3 days 160:00:00
    '2 day 100:00:00'::interval- '2 day 60:00:00'::interval; --> 40:00:00

对于这种情况，Postgres 开发人员提供了适当的函数格式标准化:

select 
    justify_hours('1 day -01:00:00'),  --> 23:00:00
    justify_hours('3 days 160:00:00'), --> 9 days 16:00:00
    justify_hours('40:00:00');         --> 1 day 16:00:00

然而他们认为没有必要进行相反的操作。在这个答案我提出了一个将间隔的日期部分转换为小时的函数。我认为它可以是（有一些微小的改变）某种反向功能 for justify_hours():

create or replace function unjustify_hours(interval)
returns interval language sql as $$
    select format('%s:%s',
        (extract (epoch from $1) / 3600)::int,
        to_char($1, 'mi:ss'))::interval;
$$;

select 
    unjustify_hours('23:00:00'),        --> 23:00:00
    unjustify_hours('9 days 16:00:00'), --> 232:00:00
    unjustify_hours('1 day 16:00:00');  --> 40:00:00

功能to_char(interval, text)在这里没有帮助，因为

select 
    to_char(interval '23:00:00', 'hh24:mi:ss'),        --> 23:00:00
    to_char(interval '9 days 16:00:00', 'hh24:mi:ss'), --> 16:00:00 (!)
    to_char(interval '1 day 16:00:00',  'hh24:mi:ss'); --> 16:00:00 (!)

请注意，可以通过多种方式正确格式化间隔：

select 
    justify_hours('100:00:00'),        --> 4 days 04:00:00
    justify_hours('1 days 76:00:00'),  --> 4 days 04:00:00
    justify_hours('2 days 52:00:00'),  --> 4 days 04:00:00
    justify_hours('5 days -20:00:00'); --> 4 days 04:00:00

Per 文档:

根据 SQL 标准，区间值的所有字段都必须 具有相同的符号，因此前导负号适用于所有字段； 例如间隔文字 '-1 2:03:04' 中的负号 适用于天和小时/分钟/秒部分。 PostgreSQL 允许田地有不同的标志，并且传统上对待 文本表示中的每个字段都是独立签名的，因此 小时/分钟/秒部分被认为是积极的 例子。如果 IntervalStyle 设置为 sql_standard，则为前导符号 被认为适用于所有领域（但前提是没有附加标志 出现）。否则使用传统的 PostgreSQL 解释。 为避免歧义，建议附加明确的标志 每个字段（如果有任何字段为负）。

and

内部间隔值存储为月、日和秒。 这样做是因为一个月中的天数不同，并且一天 如果夏令时调整为 23 或 25 小时 涉及。月份和日期字段是整数，而秒字段是 字段可以存储分数。因为间隔通常是从创建的 常量字符串或时间戳减法，这种存储方法有效 在大多数情况下都很好。函数 justify_days 和 justify_hours 是 可调整超出正常范围的日期和时间 范围。