CODEWITHSUNDEEP
sqlpostgresqltimetimezonepostgresql-9.1
TomJ
TomJ

Reputation: 5479

time with time zone query not behaving has expected

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';

Upvotes: 1

Views: 525

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658907

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;

Related answer:

Upvotes: 2

Related Questions