time with time zone query not behaving has expected

Question

I have a time with time zone column, but when the timezones are different the query does not behave has expected.

The table:

CREATE TABLE timezone_table (
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  "time" time with time zone NOT NULL,
  CONSTRAINT timezone_table_pkey PRIMARY KEY (id)
);

Here the insert statement to populate:

insert into timezone_table (id, created_at, updated_at, "time")
values (1, now(), now(), '12:00:00 UTC');

The troublesome query (doesn't find the record as expected):

SELECT * FROM "timezone_table" WHERE "timezone_table"."time" = '08:00:00 -0400';

A query that works as expected (finds the record):

SELECT * FROM "timezone_table" WHERE "timezone_table"."time" = '12:00:00 -0000';

So the question is, why would the troublesome query not return the row as expected when 08:00:00 -0400 is equivalent to 12:00:00 UTC (or at least I would expect it to be equivalent)?

The weird part is if the troublesome query uses a less-than operator, it finds the row no problem:

SELECT * FROM "timezone_table" WHERE "timezone_table"."time" < '08:00:00 -0400';

The SQL standard seems to do some very weird stuff with time zone comparisons. I don't remember the details now, but it is along the lines of 'times are stored in UTC and effectively compared in UTC, except that the time zone offset is still relevant for equality comparison'. — Jonathan Leffler, Aug 12 '14 at 21:55
`time with time zone` is frankly awful, and should be avoided. The PostgreSQL documentation even says as much. Use a `timestamptz` where possible; where not, store a `time` and an offset. — Craig Ringer, Aug 13 '14 at 00:53

score 2 · Accepted Answer · edited May 23 '17 at 11:57

The reason is, as mentioned in the comments, that timetz is broken by design. Don't use it. Postgres advises against it, per documentation:

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.

Most importantly, it completely breaks with DST (daylight saving time).
^{It's up for debate which concept is more more thoroughly broken, DST or timetz.}

I suggest timestamptz instead. Just cast to retrieve the time component (according to the local time zone): ts_value::time.

While stuck with your design, use the AT TIME ZONE construct to make the query "work":

SELECT * FROM timezone_table
WHERE "time" = '08:00:00 -0400'::timetz AT TIME ZONE 'UTC';

Or, to make it work universally (more or less - remember, the concept is broken):

SELECT * FROM timezone_table
WHERE "time" AT TIME ZONE 'UTC' = '08:00:00 -0400'::timetz AT TIME ZONE 'UTC';

This little demo should help to explain:

SELECT t1, t2
      ,t1 = t2 AS test_fail
      ,t1 AT TIME ZONE 'UTC' = t2 AT TIME ZONE 'UTC' AS test_ok
FROM ( SELECT '12:00:00 UTC'::timetz   AS t1
             ,'08:00:00 -0400'::timetz AS t2) sub;

time with time zone query not behaving has expected

1 Answers1