Properly handle TIME WITH TIME ZONE in PostgreSQL

Question

We have a table that is filled with data from a legacy report of another system. The columns of that table reflect the same structure of the report.

Here are a abbreviated structure of the table:

CREATE TABLE IF NOT EXISTS LEGACY_TABLE (
  REPORT_DATE DATE NOT NULL,
  EVENT_ID BIGINT PRIMARY KEY NOT NULL,
  START_HOUR TIMESTAMP WITHOUT TIME ZONE,
  END_HOUR TIME WITHOUT TIME ZONE,
  EXPECTED_HOUR TIME WITHOUT TIME ZONE
);

We are refactoring this table to deal with different time zones of different clients. The new structure would be something like:

CREATE TABLE IF NOT EXISTS LEGACY_TABLE (
  REPORT_DATE DATE NOT NULL,
  EVENT_ID BIGINT PRIMARY KEY NOT NULL,
  START_HOUR TIMESTAMP WITH TIME ZONE,
  END_HOUR TIME WITH TIME ZONE,
  EXPECTED_HOUR TIME WITH TIME ZONE
);

These hour fields represents a specific point in time during the day represented by the REPORT_DATE column. What I mean by that is that every TIME column represents a moment during the day specified in REPORT_DATE.

Some other points to consider:

• We don't know why the START_HOUR is in TIMESTAMP format in the report we receive from the legacy system. But we import the data the way it comes to us.
• The fields in the report are formatted according to the timezone of the client, so to refactor this table we need to combine the timezone of the client (we have this info) to properly insert the timestamps/times in UTC.

But now to the problem. The value of these columns are used to compute another values multiple times in our system, something like the following:

START_HOUR - END_HOUR (the result of this operation is currently being casted to TIME WITHOUT TIME ZONE)
START_HOUR < END_HOUR
START_HOUR + EXPECTED_HOUR
EXPECTED_HOUR - END_HOUR
EXPECTED_HOUR < '05:00' 

After some research I found that is not recommended to use the type TIME WITH TIME ZONE (Postgres time with time zone equality) and now I'm a bit confused about what is the best way to refactor this table to deal with different time zones and handle the different column operations that we need to.

Besides that, I already know that is safe to subtract two columns of type TIMESTAMP WITH TIME ZONE. This subtraction operation is taking into account DST changes (Subtracting two columns of type timestamp with time zone) but how about the others? And the one subtracting a TIME from a TIMESTAMP?.

And about the table refactoring, should we use TIME WITH TIME ZONE anyways? Should we continue using TIME WITHOUT TIME ZONE? Or is better to forget the type TIME altogether and combine the DATE with the TIME and change the columns to TIMESTAMP WITH TIME ZONE?

I think these questions are related because the new column types we choose to use, will define how we operate with the columns.

Answer 1

You asserted that:

every TIME column represents a moment during the day specified in REPORT_DATE.

So you never cross the a dateline within the same row. I suggest to save 1x date 3x time and the time zone (as text or FK column):

CREATE TABLE legacy_table (
   event_id      bigint PRIMARY KEY NOT NULL
 , report_date   date NOT NULL
 , start_hour    time
 , end_hour      time
 , expected_hour time
 , tz            text  -- time zone
);

Like you already found, timetz (time with time zone) should generally be avoided. It cannot deal with DST rules properly (daylight saving time).

So basically what you already had. Just drop the date component from start_hour, that's dead freight. Cast timestamp to time to cut off the date. Like: (timestamp '2018-03-25 1:00:00')::time

tz can be any string accepted by the AT TIME ZONE construct, but to deal with different time zones reliably, it's best to use time zone names exclusively. Any name you find in the system catalog pg_timezone_names.

To optimize storage, you could collect allowed time zone names in a small lookup table and replace tz text with tz_id int REFERENCES my_tz_table.

Two example rows with and without DST:

INSERT INTO legacy_table VALUES
   (1, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Vienna')  -- sadly, with DST
 , (2, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Moscow'); -- Russians got rid of DST

For representation purposes or calculations you can do things like:

SELECT (report_date + start_hour)    AT TIME ZONE tz AT TIME ZONE 'UTC' AS start_utc
     , (report_date + end_hour)      AT TIME ZONE tz AT TIME ZONE 'UTC' AS end_utc
     , (report_date + expected_hour) AT TIME ZONE tz AT TIME ZONE 'UTC' AS expected_utc
     -- START_HOUR - END_HOUR
     , (report_date + start_hour) AT TIME ZONE tz
     - (report_date + end_hour)   AT TIME ZONE tz AS start_minus_end
FROM   legacy_table;

You might create one or more views to readily display strings as needed. The table is for storing the information you need.

Note the parentheses! Else the operator + would bind before AT TIME ZONE due to operator precedence.

And behold the results:

db<>fiddle here

Since the time is manipulated in Vienna (like any place where silly DST rules apply), you get "surprising" results.

Related:

• Accounting for DST in Postgres, when selecting scheduled items
• Ignoring time zones altogether in Rails and PostgreSQL