如何在Postgres中创建一个包含上限的新日期范围类型？

Question

Postgres提供了一个很好的功能叫做范围类型，它提供了有用的范围功能（重叠、包含等）。

我想使用daterange类型，但是我认为该类型的实现选择有些笨拙：日期范围的上限是排除在外的。这意味着如果我将值定义为2014/01/01 - 2014/01/31，则显示为[2014/01/01, 2014/01/31)，1月31日被排除在范围之外！

我认为这是错误的默认选择。我无法想象在现实生活中有任何应用或参考会假定日期范围的结束日期是被排除在外的。至少在我的经验中没有。

我想实现一个包含下限和上限的日期范围类型，但我遇到了Postgres文档难题：关于如何创建新的离散范围类型的参考资料很晦涩，并且缺乏任何示例（从文档中获取：创建规范函数有点棘手，因为必须在声明范围类型之前定义它）。

有人能提供一些帮助吗？甚至直接提供实现本身；它应该是5-10行代码，但将这些5-10行代码组合起来需要进行严肃的研究。

编辑：澄清：我正在寻找有关如何创建正确类型的信息，以便插入 [2014/01/01，2014/01/31] 导致upper(daterange) ='2014/01/31'。使用现有的daterange类型，此值被“转换”为 [2014/01/01，2014/02/01)并给出upper(daterange) ='2014/02/01'

- dtheodor

---

Postgres范围类型始终被转换为规范形式[)。如果您需要呈现[A,B]，请在向用户呈现之前处理该值。 - Matthew Schinckel

正确。Postgres也说您可以定义范围类型，其规范形式不是默认的[)，而是任何您希望的形式。我正在寻找可以缓解我的应用程序需要执行该处理的类型，现在我必须在查询和插入数据时都执行它。 - dtheodor

我想不出在现实生活中有哪个应用或参考是包括结束日期的。如果我在酒店预订房间，我不会在结束日期入住，但我会在到达日期入住，所以结束日期不包括在内。这就是为什么它是默认设置。 - dalore

预订是我所说的反例。不过，想象一下任何类型的交互式报告。例如，您正在查看公司的销售报告，并且有一个日期范围下拉菜单，允许您筛选特定日期。您单击开始和结束日期，并且始终意味着包括它们两个。当我写这篇文章时，我正在处理的应用程序要求用户为他们选择的日期范围输入特定数据，并且选择结束日期始终意味着包括它。 - dtheodor

@MatthewSchinckel的答案是正确的。日期范围的输出规范形式为[)。 例如选择daterange('20200101'，'20201220'，[]）作为range1的返回输出为[2020-01-01，2020-12-21） 和 选择daterange('20200101'，'20201220'，（））作为range2的返回输出为[2020-01-02，2020-12-21） - RKA

Answer 1

注意第三个构造函数参数:

select daterange('2014/01/01', '2014/01/31', '[]');
        daterange        
-------------------------
 [2014-01-01,2014-02-01)

或者直接使用包含上限的类型转换：

select '[2014/01/01, 2014/01/31]'::daterange;
        daterange        
-------------------------
 [2014-01-01,2014-02-01)

编辑

不是一个新类型（我认为这种方法是错误的），而是一个适当的函数：

create function inclusive_upper_daterange(dtr daterange)
returns date as $$

    select upper(dtr) - 1;

$$ language sql immutable;

select inclusive_upper_daterange('[2014/01/01, 2014/01/31]'::daterange);
 inclusive_upper_daterange 
---------------------------
 2014-01-31

- Clodoaldo Neto

---

好的，我可以这样做。但是当浏览表格时，仍然会返回[2014-01-01,2014-02-01)的输出，并且upper(daterange)返回2014-02-01。我已经在我的应用程序中实现了所有这些解决方法。构建不需要任何此类解决方法的正确类型才是我要寻找的答案。 - dtheodor

我刚刚意识到，您编辑了我的问题标题，改变了它的含义，然后回答了那个被修改后的含义。 - dtheodor

@dtheodor 对于编辑我感到抱歉。我明白你一开始就是在寻找这个。你正在混淆数据呈现和意义，这应该在不同的层面上处理。 - Clodoaldo Neto

我尝试通过新的编辑来澄清，希望现在更清楚了。 - dtheodor

抱歉，我真的在寻找如何声明新的离散范围类型的信息，而不是其他解决方法。我自己努力去弄清楚了，并接受了自己的答案。 - dtheodor

Answer 2

根据Postgres文档的说明，我编写了以下代码来创建所需的类型。然而它无法正常工作（请继续阅读）。

CREATE TYPE daterange_;

CREATE FUNCTION date_minus(date1 date, date2 date) RETURNS float AS $$
    SELECT cast(date1 - date2 as float);
$$ LANGUAGE sql immutable;

CREATE FUNCTION dr_canonical(dr daterange_) RETURNS daterange_ AS $$
BEGIN
    IF NOT lower_inc(dr) THEN
        dr := daterange_(lower(dr) + 1, upper(dr), '[]');
    END IF;
    IF NOT upper_inc(dr) THEN
        dr := daterange_(lower(dr), upper(dr) - 1, '[]');
    END IF;
    RETURN dr;
END;
$$ LANGUAGE plpgsql;

CREATE TYPE daterange_ AS RANGE (
    SUBTYPE = date,
    SUBTYPE_DIFF = date_minus,
    CANONICAL = dr_canonical    
);

据我所知，这个定义完全遵循规范。然而，在使用ERROR: SQL function cannot accept shell type daterange_声明dr_canonical函数时失败了。 看起来 (代码也是如此)，似乎不可能使用除C语言以外的任何语言声明规范函数！因此，如果您使用的是没有访问运行机器权限的Postgres云服务，那么声明新的离散范围类型就几乎不可能了。Postgres做得好。

- dtheodor

Answer 3

使用PostgreSQL 11，您可以使用upper_inc函数解决演示部分问题，例如：

select
    WHEN upper_inc(mydaterange) THEN upper(mydaterange)
    ELSE date(upper(mydaterange)- INTERVAL '1 day')
 END 

- Gardella Juan

Answer 4

我设法创建了一个适合日期范围的自定义类型：

    CREATE or replace FUNCTION to_timestamptz(arg1 timestamptz, arg2 timestamptz) RETURNS float8 AS
    'select extract(epoch from (arg2 - arg1));' LANGUAGE sql STRICT
                                                             IMMUTABLE;
    ;
    create type tsrangetz AS RANGE
    (
      subtype = timestamptz,
      subtype_diff =
      to_timestamptz
    )
    ;
    select tsrangetz(current_date, current_date + 1)
    --["2020-10-05 00:00:00+07","2020-10-06 00:00:00+07")
    ;

- hardstep83

---

请将注释翻译成英文，例如“我成功创建了一个日期范围的自定义类型”，并添加一些描述/澄清您的代码，看起来很好。 - Nowhere Man