主页 / coding / 问题 / 79560077
Accepted
oemer ok
Asked: 2025-04-07 21:58:39 +0800 CST

为什么我检查的输入日期没有被选中？

  • 772

我有一个条件，我想获得两个日期之间的结果：

start_time: 2025-01-02
end_time: 2025-04-27

问题是我没有得到结果2025-04-27，我得到了2025-04-26，所以它提前一天结束。我还想获取时间2025-04-27

我做错了什么？

SELECT  l.id AS aslocation_id
      , l.name
      , lo.id
      , lo.start_time
      , lo.end_time
      , lo.leader_id
      , lo.is_storno
      , lo.storno_at
      , lo.storno_by
      , lo.employees_need
      , lo.created_at
FROM location_orders AS lo
JOIN location AS l ON l.id = lo.location_id
WHERE lo.tenant_id = 321 
  AND lo.deleted_by IS NULL 
  AND lo.start_time >= '2025-01-02' 
  AND lo.end_time <= '2025-04-27'
ORDER BY lo.start_time DESC
       , lo.id DESC
LIMIT 50;
sql

3 个回答

  1. Best Answer

    你可以在查询语句前添加 ，让 Postgres 为你解释explain analyze verbose。执行完之后，它会显示一个执行计划，其中包含类似以下内容：

    Index Cond: ((lo.start_time >= '2025-01-02 00:00:00'::timestamp without time zone) AND (lo.end_time <= '2025-04-27 00:00:00'::timestamp without time zone))

    它使你的'2025-01-02'和'2025-04-27'成为timestamp类型，而不是date。发生这种情况是因为你的start_time和end_time列timestamp已经是 s ，并且传入的常量是 类型unknown。

    您可以看到左侧pg_operator有 3 种不同的>=运算符变体可用于进行比较：timestamp

    select oprname
     , oprleft::regtype
     , oprright::regtype
     , oprcode
from pg_operator 
where oprname='>=' 
  and oprleft='timestamp'::regtype;
    操作员名称 左派 版权 操作码
    >= 不带时区的时间戳 日期 timestamp_ge_date
    >= 不带时区的时间戳 不带时区的时间戳 时间戳
    >= 不带时区的时间戳 带时区的时间戳 timestamp_ge_timestamptz

    此列表用于猜测右操作数（unknown字符串常量）的正确类型。虽然有一个列表接受date右侧的 a ，但类型转换规则 2.a指出最好使它们匹配：

    如果二元运算符调用的一个参数属于该unknown类型，则假定它与此检查的另一个参数属于同一类型。

    即使它没有被转换并且您明确地进行了转换<= '2025-04-27'::date，当与您的timestamp字段进行比较时，右侧的日期字符串仍算作该日期的午夜。

    正如已经提到的，最简单的方法是排除上限并提高日期：
    db<>fiddle 上的演示

    explain analyze verbose
SELECT  l.id AS aslocation_id
      , l.name
      , lo.id
      , lo.start_time
      , lo.end_time
      , lo.leader_id
      , lo.is_storno
      , lo.storno_at
      , lo.storno_by
      , lo.employees_need
      , lo.created_at
FROM location_orders AS lo
JOIN location AS l ON l.id = lo.location_id
WHERE lo.tenant_id = 321 
  AND lo.deleted_by IS NULL 
  AND lo.start_time >= '2025-01-02' 
  AND lo.end_time < '2025-04-28' -----------here
ORDER BY lo.start_time DESC
       , lo.id DESC
LIMIT 50;
    • 3

  2. 问题在于这个表达式：

    lo.end_time <= '2025-04-27'

    它相当于这样：

    lo.end_time <= '2025-04-27 00:00:00.000'

    因此，任何end_time在一天的第一时刻之后的时间成分值都被排除在外。

    解决方案是半开放范围，为所需日期的后一天设置独占（而不是包含）上限：

    lo.end_time < '2025-04-28'

    注意<而不是<=比较运算符。

    这比其他地方建议的强制转换为 Cate 更好lo.end_time，因为强制转换选项会强制数据库计算表中符合其他条件的每一行的转换结果，即使是您不需要的行。否则，它如何完成条件检查以判断您是否需要该行？此外，Date 转换往往会干扰此列的任何索引，而这些索引本来可能会有所帮助，这会损害数据库性能的核心。

    • 0

  3. 您遇到的问题很可能是由于 SQL 查询中处理日期和时间的方式造成的。该lo.end_time <= '2025-04-27'条件仅包含lo.end_time恰好位于 开头的记录2025-04-27 00:00:00。如果lo.end_time包含当天晚些时候的任何时间成分（例如2025-04-27 12:00:00），则会被排除。

    为确保包含截至 2025 年 4 月 27 日的所有记录，

    1. 调整结束时间以包含一整天

    2025-04-27您可以通过lo.end_time与以下内容进行比较来调整查询以包含一整天2025-04-28 00:00:00：

    WHERE lo.tenant_id = 321 
  AND lo.deleted_by IS NULL 
  AND lo.start_time >= '2025-01-02' 
  AND lo.end_time < '2025-04-28'

    2.使用日期函数来包含一整天

    WHERE lo.tenant_id = 321 
  AND lo.deleted_by IS NULL 
  AND lo.start_time >= '2025-01-02' 
  AND DATE(lo.end_time) <= '2025-04-27'

    **从 chatgpt 获取帮助

    • -1

相关问题