从范围类型中提取日期

从范围类型值中提取日期或间隔

库代码片段

从范围类型中提取日期

适用于 PostgreSQL

9.2+

用

SQL

依赖于

无

确定时间戳 (tz) 范围的下界和上界之间的间隔，或返回下界和下界之间的天数。

函数

两组重载函数，第一组用于提取间隔，第二组用于提取天数。PostgreSQL 关于范围类型的文档：https://postgresql.ac.cn/docs/current/static/rangetypes.html

CREATE OR REPLACE FUNCTION extract_interval(TSTZRANGE) RETURNS interval AS
$func$
    select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION extract_interval(TSRANGE) RETURNS interval AS
$func$
    select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;


CREATE OR REPLACE FUNCTION extract_days(TSTZRANGE) RETURNS integer AS
$func$
    select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION extract_days(TSRANGE) RETURNS integer AS
$func$
    select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

示例

WITH tzr AS (SELECT tstzrange('2013-10-01 10:00-07', '2013-10-03 05:15-07') AS dttz, tstzrange('2013-10-01 10:00', '2013-10-03 05:15') AS dt)
SELECT  extract_interval(dttz) as interval_with_tz,
        extract_interval(dt) as interval_no_tz,
        extract_days(dttz) as days_with_tz,
        extract_days(dt) as days_no_tz
FROM tzr;

 interval_with_tz | interval_no_tz | days_with_tz | days_no_tz 
------------------+----------------+--------------+------------
 1 day 19:15:00   | 1 day 19:15:00 |            3 |          3