postgresql - 在 Postgres 中使用带有时间戳值的 BETWEEN 运算符

Question

最近 StackOverflow 用户通知我使用 BETWEEN数据类型为 timestamp without time zone 的运算符不应使用。以下是引述。

Between means >= and <= and shall not be used with ranges that contain timestamps.

当被问及对这篇论文的解释或指向 Postgres 文档的链接时，它指出我已经得到了答案

Why would such a simple thing need a site with documentation. I am sure you can find many anyway if you google (at least my detailed posts on various forums demonstrating the case)

好吧，我用谷歌搜索了。并没有发现任何建议不要将此运算符与时间戳值一起使用。事实上this answer在 SO 上使用它们，this mailing group post 也是如此.

我被告知这些年来我做错了。真的是这样吗？

据我所知，Postgres 的时间戳最大精度是 1 microsecond - 如我错了请纠正我。因此，下面的陈述不是等价的吗？

sample_date BETWEEN x AND y::timestamp - INTERVAL '1 microsecond'

和

sample_date >= x AND sample_date < y

编辑:示例只是对差异的考虑。我知道开发人员可能会错过时间部分这一事实，但假设有人知道它的行为方式，为什么它应该不被使用？一般来说，这只是一个示例，但我想知道更大的范围。我一直在调查规划器，它似乎正在解析 BETWEEN至 >= AND <= .

为什么最好写 >= AND <=比BETWEEN关于结果 - 不包括翻译时间吗？

Answer 1

使用ts BETWEEN validfrom AND validto 绝对没有错而不是 ts >= validform AND ts <= validto .它们是相同的。

我只能猜测，但我想说警告针对的是不同的东西，即上面的(相同)子句中的任何一个是否是正确的使用。

现在这当然取决于您要做什么，但是像这样的子句通常用于识别特定时间戳的一个有效行。在那种情况下，上面的子句是错误的，因为对于 ts 的值更改行时，您将得到两个 个结果。

考虑一下:

CREATE TABLE names (
   id integer PRIMARY KEY,
   val text NOT NULL,
   validfrom timestamptz NOT NULL,
   validto timestamptz NOT NULL
);

INSERT INTO names VALUES (1, 'Smith', '1985-05-02 00:00:00', '2009-01-30 00:00:00');
INSERT INTO names VALUES (2, 'Jones', '2009-01-30 00:00:00', 'infinity');

这是一个人名的历史表。

如果您使用 WHERE像上面的子句查询在特定时间有效的名称，它适用于

SELECT val FROM names
WHERE current_timestamp BETWEEN validfrom AND validto;

但它会做错事

SELECT val FROM names
WHERE '2009-01-30' BETWEEN validfrom AND validto;

那是因为名称有效期区间的终点不是区间的一部分。对于这种情况，这样写是正确的:

SELECT val FROM names
WHERE '2009-01-30' >= validfrom AND '2009-01-30' < validto;