具有时区相等性的 Postgres 时间 [英] Postgres time with time zone equality

问题描述

我在 Postgres 中遇到了 time with time zone 等式问题.timestamp with time zone 相等性按照我的预期工作，如果在规范化时区后时间相同，则应该是真的:

I'm having some trouble with time with time zone equalities in Postgres. timestamp with time zone equality works how I would expect it to, where if the times are the same after normalizing the timezones, it should be true:

postgres=# select '2013-06-27 12:00:00 -0800'::timestamp with time zone = '2013-06-27 14:00:00 -0600'::timestamp with time zone;
 ?column?
----------
 t

但是，这似乎不适用于 time with time zone:

However, the same does not seem to apply to time with time zone:

postgres=# select '12:00:00 -0800'::time with time zone = '14:00:00 -0600'::time with time zone;
 ?column?
----------
 f

然而，不平等会按照我的预期发挥作用:

Yet inequalities work how I would expect them to:

postgres=# select '12:00:00 -0800'::time with time zone < '14:01:00 -0600'::time with time zone;
 ?column?
----------
 t

postgres=# select '12:00:00 -0800'::time with time zone > '13:59:00 -0600'::time with time zone;
 ?column?
----------
 t

我对 time with time zone 有什么误解吗?如何以与 timestamp with time zone 相等性相同的方式处理时区来评估相等性?

Is there something I'm misunderstanding about time with time zone? How can I evaluate for equality in a way that handles time zones the same way timestamp with time zone equality does?

推荐答案

这里有两种评估 timetz 相等性的方法:

Here are two ways to evaluate timetz equality:

SELECT a, b, a = b AS plain_equality
     , '2000-1-1'::date + a = '2000-1-1'::date + b AS ts_equality
     , a AT TIME ZONE 'UTC', b AT TIME ZONE 'UTC'  AS timetz_equality
FROM  (
   SELECT '12:00:00 -0800'::timetz AS a
        , '14:00:00 -0600'::timetz AS b
   ) sub;

首先将其添加到 date.
第二个使用 AT TIME ZONE 构造.

The first by adding it to a date.
The second by using the AT TIME ZONE construct.

但完全不要使用 time with time zone.
Postgres 支持该类型只是因为它在 SQL 标准中.它被设计破坏(不能考虑 DST！)并且不鼓励使用它.

But rather don't use time with time zone at all.
Postgres supports the type only because it is in the SQL standard. It is broken by design (cannot consider DST!) and its use is discouraged.

引用手册这里:

time with time zone 类型由 SQL 标准定义，但定义展示了导致可疑有用性的属性.在大多数情况下，date、time、timestamp without time zone 和 timestamp with time zone 的组合应该提供完整的范围任何应用程序所需的日期/时间功能.

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

这篇关于具有时区相等性的 Postgres 时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！