为什么postgres为相同的间隔值显示两种不同的格式? [英] Why postgres show two different format for same interval value?

问题描述

我正在帮助解决这个 问题 尝试更改间隔的格式.

I was helping with this question trying to change the format for the interval.

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

我提供了一个字符串处理的解决方案.但是后来我发现postgres可以以两种不同的格式显示相同的间隔.

I give a solution with string manipulation. But then I found postgres can show the same interval on two different format.

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

有趣的事情.有 函数 进行逆过程

Funny thing. There is a function doing the inverse process

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

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

So I wondering if there is a direct way to solve this problem. And why same interval value has two different result.

推荐答案

当时间间隔两个时间戳之间的差异时，始终以小时为理由(即，它具有标准 >格式).例子:

When an interval is a difference between two timestamps it is always justified to hours (i.e. it has standard format). Examples:

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

间隔计算是分别在日期部分和时间部分执行的，因此可能会导致格式奇怪.例子:

Calculations on intervals are executed on date part and time part separately, so they may lead to strange formats. Examples:

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开发人员为格式标准化提供了适当的功能:

For such cases Postgres developers provided the appropriate function for the format standardization:

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

但是，他们并不认为需要进行反向操作.在此答案中，我提出了一个将间隔的日期部分转换为小时的函数.我认为它可以(有一些小的更改)用于justify_hours()的某种反向函数:

However they did not think that the reverse operation would be needful. In this answer I proposed a function to convert a date part of an interval to hours. I think it can be (with some minor changes) some kind of reverse function 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 (!)

请注意，间隔可以通过多种方式正确设置格式:

Note that an interval can be correctly formatted in many ways:

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

每个文档:

根据SQL标准，间隔值的所有字段都必须 具有相同的符号，因此前导负号适用于所有字段； 例如区间文字"-1 2:03:04"中的负号 适用于日期和小时/分钟/秒部分. PostgreSQL的 允许字段具有不同的符号，并且传统上对待 文本表示形式中的每个字段均独立签名，因此 时分/秒/秒部分被认为是正数 例子.如果IntervalStyle设置为sql_standard，则前导符号 被认为适用于所有字段(但前提是没有其他符号 出现).否则，将使用传统的PostgreSQL解释. 为避免含糊不清，建议将显式符号附加到 每个字段是否为负.

According to the SQL standard all fields of an interval value must have the same sign, so a leading negative sign applies to all fields; for example the negative sign in the interval literal '-1 2:03:04' applies to both the days and hour/minute/second parts. PostgreSQL allows the fields to have different signs, and traditionally treats each field in the textual representation as independently signed, so that the hour/minute/second part is considered positive in this example. If IntervalStyle is set to sql_standard then a leading sign is considered to apply to all fields (but only if no additional signs appear). Otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity, it's recommended to attach an explicit sign to each field if any field is negative.

和

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

Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases. Functions justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.

这篇关于为什么postgres为相同的间隔值显示两种不同的格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！