Redshift中BETWEEN条件用同一日期为何能返回数据？

我想查询2024年1月1日的订单，误将BETWEEN条件的起止日期都设为同一日期：

SELECT *
FROM   orders
WHERE  order_date BETWEEN To_date('2024-01-01', 'yyyy-mm-dd') AND
                          To_date('2024-01-01', 'yyyy-mm-dd')

该语句成功返回了当日订单，但按日期默认时间为00:00:00的逻辑，理论上BETWEEN同一时间点应无结果，想了解背后原理。

原因解析 #

首先明确：BETWEEN a AND b是闭区间查询，会同时包含等于a和等于b的记录，并非“同一时间点无结果”——只要有记录的时间匹配这个端点值，就会被筛选出来。具体分两种常见场景：

• 场景1：order_date仅存储日期（无时分秒）
  如果order_date是MySQL的DATE类型（本身只存年月日），或是Oracle的DATE类型但插入时用TRUNC()截断了时分秒（所有记录的时分秒均为00:00:00），那么2024年1月1日的所有订单，其order_date值完全等于TO_DATE('2024-01-01', 'yyyy-mm-dd')转换后的2024-01-01 00:00:00，自然会被BETWEEN条件匹配到。

• 场景2：order_date为字符串类型（不推荐但实际存在）
  如果order_date用yyyy-mm-dd格式的字符串存储，字符串比较时2024-01-01和转换后的日期字符串完全相等，所有当日订单都会被返回。

如果你的order_date实际包含非0点的时分秒，但仍返回了当日订单，大概率是数据库做了隐式类型转换（自动截断时分秒再比较），但这种逻辑不建议依赖，更严谨的当日订单查询写法如下：

-- Oracle 写法
SELECT *
FROM   orders
WHERE  TRUNC(order_date) = TO_DATE('2024-01-01', 'yyyy-mm-dd')

-- 通用严谨写法（兼容带时分秒的场景）
SELECT *
FROM   orders
WHERE  order_date >= TO_DATE('2024-01-01', 'yyyy-mm-dd')
  AND  order_date < TO_DATE('2024-01-02', 'yyyy-mm-dd')

内容的提问来源于stack exchange，提问作者Tharaka Deshan