PostgreSQL：查询包含季度最后时刻的tstzrange

Question

假设有一个PostgreSQL表，它应该包含具有连续、不重叠的valid_range范围的行，例如：

CREATE TABLE tracking (
    id INT PRIMARY KEY,
    valid_range TSTZRANGE NOT NULL,
    EXCLUDE USING gist (valid_range WITH &&)
);

INSERT INTO tracking (id, valid_range) VALUES
    (1, '["2017-03-01 13:00", "2017-03-31 14:00")'),
    (2, '["2017-03-31 14:00", "2017-04-01 00:00")'),
    (3, '["2017-04-01 00:00",)');

这将创建一个包含以下内容的表格：

 id |                     valid_range                     
----+-----------------------------------------------------
  1 | ["2017-03-01 13:00:00-07","2017-03-31 14:00:00-06")
  2 | ["2017-03-31 14:00:00-06","2017-04-01 00:00:00-06")
  3 | ["2017-04-01 00:00:00-06",)

我需要查询在特定季度末有效的行，其中我将“在一个季度结束时”定义为“日期刚好在变为新季度第一天之前的那一瞬间”。在上面的示例中，查询2017年Q1的结尾（Q1在2017-03-31结束，Q2从2017-04-01开始），我希望我的查询仅返回ID为2的行。

在PostgreSQL中表达此条件的最佳方法是什么？

选择 SELECT * FROM tracking WHERE valid_range @> TIMESTAMPTZ '2017-03-31' 是错误的，因为它返回包含2017-03-31午夜的行，即ID 1。 valid_range @> TIMESTAMPTZ '2017-04-01' 也是错误的，因为它跳过了实际上在季度末有效的行（ID 2），而返回了ID 3的行，这是新季度的开始行。

我想避免在查询中使用像 ...ORDER BY valid_range DESC LIMIT 1 这样的东西。

请注意，范围的结束必须始终是排除的，我无法改变这一点。

- Dale

---

SELECT * FROM tracking WHERE valid_range @> TIMESTAMPTZ '2017-03-31 23:59:59'？请返回翻译后的文本。 - Abelisto

@Abelisto 不，因为那样我就可以问23:59:59.9，然后是23:59:59.99和23:59:59.999等等 :) - Dale

如果您的范围精度超过一秒钟 - 是的。但在您的示例中，它是以小时为单位的。 - Abelisto

抱歉，那只是为了方便打字，一定要假设我的时间戳可以使用所有可用的精度（它们通常会借助NOW()来生成）。 - Dale

顺便提一下，时间戳类型分辨率为1微秒，所以我认为，'2017-03-31 23:59:59.999999'就足够了 :) - Abelisto

Answer 1

到目前为止，我想到的最好答案是：

SELECT
    *
FROM
    tracking
WHERE
    lower(valid_range) < '2017-04-01'
    AND upper(valid_range) >= '2017-04-01'

这似乎是说“我想要反转此查询中TSTZRANGE列上包容性/排除性的边界”，这让我觉得有更好的方法可做。我不会感到意外，如果它也否定了范围列上典型索引的好处。

- Dale

Answer 2

您可以使用<@运算符来检查值是否在范围内：

SELECT *
FROM tracking
WHERE to_timestamp('2017-04-01','YYY-MM-DD')::TIMESTAMP WITH TIME ZONE <@ valid_range;

在线测试 PostgreSQL 查询

- Slava Rozhnev

---

我认为在我的问题中已经解释了为什么这对我不起作用？我使用您提供的链接进行了测试，无论我提供2017-03-31（返回ID 1）还是2017-04-01（返回ID 3），它都不会返回ID 2。 - Dale