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

问题描述

在Postgres中，带有时区的时间等价时遇到了麻烦。 带有时区的时间戳平等地按我期望的方式工作，如果在对时区进行归一化之后，如果时间相同，则应该为真：

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

但是，似乎不适用于带有时区的时间：

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

是否有东西我误解了时区的时间？如何用与时区相同的时间戳相等的方式来处理时区？

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;

first （通过将其添加到日期） 。通过使用 时区构造。

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

但不要使用带时区的时间。

Postgres仅支持该类型，因为它在SQL标准中。

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

引用手册此处：

类型带有时区的时间是由SQL标准定义的，但是
的定义却显示出导致有用的疑问的属性。
在大多数情况下，日期，时间，没有时间的时间戳的组合
区域和带时区的时间戳应该提供任何应用程序所需的
日期/时间功能的完整范围。

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屋！