Postgres time with time zone equality

Question

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?

Erwin Brandstetter · Accepted Answer · 2021-05-28 00:14:21Z

6

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

edited May 28, 2021 at 0:14

answered Dec 11, 2013 at 21:29

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Luiz Over a year ago

Ok, we should not use time with time zone, but how we should save a hour of day that is locale dependent? Should we use the time column and transform to UTC in the application layer or it is better indicated to combine, if available, the date with the time and use a timestamp with time zone column even if we already have a column for the date in the same table? What approach do you recommend?

Erwin Brandstetter Over a year ago

@Luiz: It depends on the information you actually need. Abstract time? Absolute point in time? Time and place? Or time and time zone? Please present your case as new question, comments are not the place. You can always link to this one for context. And drop a comment here if you want to link back.

Luiz Over a year ago

Thanks for the answer. I created a new question as you suggested. If you can take a look: stackoverflow.com/questions/50277141/…

Collectives™ on Stack Overflow

Postgres time with time zone equality

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related