比较 TIME WITH TIME ZONE 返回意外结果 [英] Comparing TIME WITH TIME ZONE returns unexpected result

问题描述

为什么这个查询返回false?是不是因为 22:51:13.202248 +01:00 格式?

Why does this query return false? Is it because of the 22:51:13.202248 +01:00 format?

SELECT now()::time at TIME ZONE 'Europe/London'; -- 22:51:13.202248 +01:00

SELECT now()::time at time zone 'Europe/London' > '22:00:00'::time
   AND now()::time < '23:35:00'::time as is_currently_open; -- false

推荐答案

now()::time at time zone 'Europe/London'

... 返回time with time zone (timetz) 的值:

... returns a value of time with time zone (timetz):

然后将其与 time [无时区] 进行比较.不要这样做.time 值在此过程中被强制为 timetz，并根据当前的 timezone 设置附加时间偏移量.意思是，您的表达式将使用不同的设置进行不同的评估.更重要的是，DST 规则没有正确应用.你什么都不想要！见:

Then you compare it to time [without time zone]. Don't do this. The time value is coerced to timetz in the process and a time offset is appended according to the current timezone setting. Meaning, your expression will evaluate differently with different settings. What's more, DST rules are not applied properly. You want none of this! See:

db<>fiddle 这里

更一般地说，不要使用 <代码>带时区的时间 (timetz).该类型已被设计破坏，并且在 Postgres 中正式不鼓励使用.见:

More generally, don't use time with time zone (timetz) at all. The type is broken by design and officially discouraged in Postgres. See:

• Postgres 时间与时区相等

改为使用:

SELECT (now() AT TIME ZONE 'Europe/London')::time > '22:00:00'
   AND (now() AT TIME ZONE 'Europe/London')::time < '23:35:00' AS is_currently_open;

正确的操作数现在可以是一个无类型的文字，它会被强制转换为 time 应该的.

The right operand can be an untyped literal now, it will be coerced to time as it should.

BETWEEN 通常是错误的时间和时间戳工具.见:

BETWEEN is often the wrong tool for times and timestamps. See:

• 如何向时间戳列添加昼/夜指示器?

但>= 和<= 似乎更适合营业时间?然后 BETWEEN 适合用例并使其更简单:

But it would seem that >= and <= are more appropriate for opening hours? Then BETWEEN fits the use case and makes it a bit simpler:

SELECT (now() AT TIME ZONE 'Europe/London')::time
       BETWEEN '22:00:00' AND '23:35:00' AS is_currently_open;

相关:

• 在 PostgreSQL 中执行这个小时的操作查询

这篇关于比较 TIME WITH TIME ZONE 返回意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！