PostgreSQL 中时间区间查询的 BETWEEN 边界问题

sql -- 表 orders，字段 order_time (timestamp) -- 示例数据： -- 2024-03-15 08:00:00 -- 2024-03-15 18:30:00 -- 2024-03-16 00:00:00

-- 错误写法：期望拿到 3月15日全天的数据 SELECT * FROM orders WHERE order_time BETWEEN '2024-03-15' AND '2024-03-15'; -- 结果：0 行 （因为 '2024-03-15' = 2024-03-15 00:00:00）

## 日期类型 (`date`) 的隐藏转换
当列类型为 `date` 时，`BETWEEN` 似乎能正常工作，因为 `date` 没有时间部分。但一旦与时间戳进行跨类型比较，PostgreSQL 会按优先级将 `date` 转为 `timestamp`（午夜时刻），同样会掉入上述陷阱。

```sql
-- 列 created_date 类型是 date
-- 假设查询想包含结束日期当天的所有数据
SELECT * FROM events
WHERE created_date BETWEEN '2024-03-10'::date AND '2024-03-15'::date;
-- 可行，因为 '2024-03-15' 的边界就是当天，date 之间比较没有时间问题。

安全的显式区间写法

放弃 BETWEEN 的模糊性，改用明确的不等式，可以精确控制包含或不包含右边界。这是处理时间区间的推荐实践。

半开区间模式（>= 和 <）

使用半开区间 [start, end)，即包含起点，但不包含次日零点。

-- 查询 2024-03-15 整天的订单
SELECT * FROM orders
WHERE order_time >= '2024-03-15'::date
  AND order_time <  '2024-03-16'::date;   -- 次日零点之前

这种方法无论列精度如何都不会漏掉数据，且能充分利用时间列上的索引。

使用 date_trunc 明确精度需求

如果业务只关心“日期”而不考虑具体时分秒，可以将 timestamp 截断到天。

SELECT * FROM orders
WHERE date_trunc('day', order_time) = '2024-03-15'::date;

注意：date_trunc 会阻止索引直接使用，若数据量大需考虑函数索引或改用范围比较。

处理时区带来的边界偏移

当列类型为 timestamptz 时，字符串输入会被解释为客户端所在时区的午夜。若服务器和客户端时区不一致，'2024-03-15' 可能对应 UTC 时间的前一天某时刻，导致区间偏移。

最佳实践：显式定义时区

-- 先明确指定时区，再转换为范围
SELECT * FROM orders
WHERE order_time >= '2024-03-15'::date AT TIME ZONE 'Asia/Shanghai'
  AND order_time <  '2024-03-16'::date AT TIME ZONE 'Asia/Shanghai';