sql - 时区相等的 Postgres 时间

Question

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

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:

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

然而，不平等的运作方式与我期望的一样:

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 equality 相同？

Answer 1

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

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 construct .

而是根本不要使用time with time zone。
Postgres 支持该类型只是因为它符合 SQL 标准。它被设计破坏(不能考虑 DST!)并且不鼓励使用它。

引用手册here :

The type time with time zone is defined by the SQL standard, but thedefinition 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 ofdate/time functionality required by any application.