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
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
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?
Advertisement
Answer
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;
The first by adding it to a date
.
The second by using the AT TIME ZONE
construct.
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.
Quoting the manual here:
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 ofdate
,time
,timestamp without time zone
, andtimestamp with time zone
should provide a complete range of date/time functionality required by any application.