主页 / dba / 问题 / 152929
Accepted
beldaz
Asked: 2016-10-21 15:11:02 +0800 CST

PostgreSQL 区间划分

  • 772

这已经出现过几次，例如，在postgresql 新闻组和wiki中。一般来说，不同intervals 之间的关系可能没有很好地定义 - 一个月可以是不同的天数，具体取决于正在考虑的月份（和年份）。但有时需要计算两个时间点之间发生了多少间隔，例如（简化示例）：

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;

这在 PostgreSQL 中是不允许的，因为它是两个间隔之间的划分，由于上述原因，它没有明确定义的一般行为。当间隔可以转换为秒时，存在一种解决方法，但是如果不是这种情况，例如，当间隔为毫秒级时，最好的方法是什么？

postgresql time

4 个回答

  1. Best Answer

    正如@a_horse_with_no_name 在他的评论中提到的那样，除法可以通过转换为毫秒来实现。

    SELECT 
  (1000*EXTRACT(EPOCH FROM tend - tstart) 
    + EXTRACT(MILLISECOND FROM tend - tstart))
  / (1000*EXTRACT(EPOCH FROM ticklength) 
    + EXTRACT(MILLISECOND FROM ticklength)) AS numticks
FROM recordings;
    • 3

  2. 您链接到的解决方法（从间隔中提取纪元并除以它们）在几秒钟和几毫秒内都可以正常工作。因此，如果这是您的用例，请使用它。

    但是，如果您想更通用并在无法回答时返回 NULL，我们必须更复杂：

    -- An interval consists of a number of years, months, days, hours,
-- minutes, and seconds.  The problem of dividing two intervals lies
-- in the fact that "one month" is not a fixed number of days.
-- Therefore, dividing, say, P1M with P1D would not make sense.

CREATE OR REPLACE FUNCTION interval_months_part(INTERVAL)
        RETURNS INTEGER AS $$
        SELECT EXTRACT(years FROM $1)::INTEGER * 12
                + EXTRACT(months FROM $1)::INTEGER
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Years plus months as a whole number of months';

CREATE OR REPLACE FUNCTION interval_seconds_part(INTERVAL)
        RETURNS DOUBLE PRECISION AS $$
        SELECT EXTRACT(days FROM $1) * 24 * 60 * 60
                + EXTRACT(hours FROM $1) * 60 * 60
                + EXTRACT(mins FROM $1) * 60
                + EXTRACT(secs FROM $1);
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Days, hours, minutes, and seconds as seconds';

-- If we can divide exactly, do so.  Otherwise return NULL, unless
-- fudging is requested, in which case all months are implicitly
-- assumed to be 30 days, (except for 12 months, which become 365.25
-- days), by extracting and dividing the epochs.
CREATE OR REPLACE FUNCTION interval_divide(dividend INTERVAL,
                                                divisor INTERVAL,
                                                fudge BOOL DEFAULT false)
        RETURNS DOUBLE PRECISION AS $$
        SELECT CASE WHEN interval_months_part($1) = 0
                                AND interval_months_part($2) = 0
                        THEN interval_seconds_part($1)
                                / interval_seconds_part($2)
                        WHEN interval_seconds_part($1) = 0
                                AND interval_seconds_part($2) = 0
                        THEN CAST(interval_months_part($1)
                                        AS DOUBLE PRECISION)
                                / interval_months_part($2)
                        WHEN fudge
                                THEN EXTRACT(EPOCH FROM $1)
                                        / EXTRACT(EPOCH FROM $2)
                        ELSE NULL
                        END;
$$ LANGUAGE SQL IMMUTABLE STRICT;
    • 1

  3. 虽然目前 Postgres 似乎不支持间隔除法，但您当然可以将这些间隔转换为可以除法的数字 - 例如秒。

    其他建议的答案建议：EXTRACT(MILLISECOND FROM interval '1 day')，但我发现这有时会返回0。当然，一个人不能被零除。

    但以下工作更好，更可靠：

    SELECT EXTRACT(EPOCH FROM interval '17 days') / EXTRACT(EPOCH FROM interval '1 day');

    EXTRACT(EPOCH, interval)所以要划分间隔 - 使用并划分这些间隔中的秒数。

    • 1

  4. 接受的答案是错误的。

    我认为您不应该添加从间隔中提取的毫秒数，因为从间隔中提取的时期已经以毫秒为单位提取秒数。文档说：

    epoch -- [...] 对于间隔值，间隔中的总秒数

    毫秒 - 秒字段，包括小数部分，乘以 1000。请注意，这包括整秒。

    你可以试试：

    select extract('epoch' from '1987 years 6 months 5 days 8 hours 15 min 30.123 seconds'::interval);

    date_part
-----------------
 62720964930.123

    您看到这里的“.123”如何告诉我们毫秒已经包括在内了吗？

    我认为为间隔添加除法运算符早就应该了。

    CREATE OR REPLACE FUNCTION interval_div(interval, interval) RETURNS float8 AS $$
  SELECT EXTRACT(EPOCH FROM $1) / EXTRACT(EPOCH FROM $2)
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OPERATOR / ( 
   FUNCTION = interval_div,
   LEFTARG = interval, 
   RIGHTARG = interval );

    有了这个：

    select '1 hour'::interval / '1 second'::interval;
 ?column?
----------
     3600

select '1 hour'::interval / '1.123 second'::interval;
     ?column?
-------------------
 3205.699020480855

    一些警告：

    select '1 month'::interval / '1 day'::interval;
 ?column?
----------
       30

integrator=> select '12 month'::interval / '1 day'::interval;
 ?column?
----------
   365.25

    儒略年，但不是平均儒略月。

    select '1 year'::interval / '1 day'::interval / 12;
 ?column?
----------
  30.4375
    • 0

相关问题