title: Properly handle TIME WITH TIME ZONE in PostgreSQL
body: <p>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.</p>
  
  <p>Here are a abbreviated structure of the table:</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>We are refactoring this table to deal with different time zones of different clients. The new structure would be something like:</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>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.</p>
  
  <p>Some other points to consider:</p>
  
  <ul>
  <li>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.</li>
  <li>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.</li>
  </ul>
  
  <p>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:</p>
  
  <pre><code>START_HOUR - END_HOUR (the result of this operation is currently being casted to TIME WITHOUT TIME ZONE)
  START_HOUR &lt; END_HOUR
  START_HOUR + EXPECTED_HOUR
  EXPECTED_HOUR - END_HOUR
  EXPECTED_HOUR &lt; '05:00' 
  </code></pre>
  
  <p>After some research I found that is not recommended to use the type <code>TIME WITH TIME ZONE</code> (<a href="https://stackoverflow.com/questions/20529284/postgres-time-with-time-zone-equality/20530283">Postgres time with time zone equality</a>) 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.</p>
  
  <p>Besides that, I already know that is safe to subtract two columns of type <code>TIMESTAMP WITH TIME ZONE</code>. This subtraction operation is taking into account DST changes (<a href="https://stackoverflow.com/questions/50223332/subtracting-two-columns-of-type-timestamp-with-time-zone">Subtracting two columns of type timestamp with time zone</a>) but how about the others? And the one subtracting a TIME from a TIMESTAMP?.</p>
  
  <p>And about the table refactoring, should we use <code>TIME WITH TIME ZONE</code> anyways? Should we continue using <code>TIME WITHOUT TIME ZONE</code>? Or is better to forget the type <code>TIME</code> altogether and combine the DATE with the TIME and change the columns to <code>TIMESTAMP WITH TIME ZONE</code>?</p>
  
  <p>I think these questions are related because the new column types we choose to use, will define how we operate with the columns.</p>