PostgreSQL中TIME WITH TIME ZONE类型的合理处理方案问询

Hey there, let's work through your time zone and table structure questions—this is a common pain point when migrating legacy systems, so I'll break it down clearly.

1. Choosing the Right Column Types for Table Refactoring #

First, let's address the elephant in the room: TIME WITH TIME ZONE is almost always a bad choice in PostgreSQL. As you found in your research, this type doesn't actually track full time zones (it only stores an offset like -04:00), which leads to confusion with DST changes and inconsistent handling across clients. So let's rule that out first.

Now, let's evaluate your remaining options:

Option A: Merge Date + Time into TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) #

This is the most robust and recommended approach for your use case. Here's why:

• Your time fields (START_HOUR, END_HOUR, EXPECTED_HOUR) all map to the same REPORT_DATE in the client's time zone. Combining these into a single TIMESTAMPTZ value means you're storing a complete, unambiguous point in time (converted to UTC under the hood).
• It eliminates ambiguity when handling different client time zones—each client can convert the UTC TIMESTAMPTZ back to their local time zone consistently.
• It simplifies all your required operations (more on that in section 2).

Implementation note: When importing legacy data, convert each client-timezone REPORT_DATE + time_field to a TIMESTAMPTZ. For example, if the client is in "America/New_York", use:

(REPORT_DATE + START_HOUR) AT TIME ZONE 'America/New_York'

This gives you the UTC-based TIMESTAMPTZ to store.

Option B: Stick with TIME WITHOUT TIME ZONE #

This is risky unless you add an explicit CLIENT_TIMEZONE column to every row. Without storing the timezone associated with each time value, you can't reliably convert times to UTC or handle different client time zones correctly. This introduces a lot of room for human error and makes your queries far more complex. Avoid this unless you have no other choice.

Option C: TIME WITH TIME ZONE #

As mentioned earlier, this type is misleading. It only stores an offset, not a full timezone identifier (like "America/New_York"). This means it can't account for DST changes automatically, and many client tools handle this type poorly. Skip this entirely.

2. Safety of Time Operations #

Let's walk through each of your required operations with the recommended TIMESTAMPTZ type, and note any caveats:

• START_HOUR - END_HOUR (convert result to TIME WITHOUT TIME ZONE):
  Subtracting two TIMESTAMPTZ values gives an INTERVAL (an exact duration). If your time differences are always less than 24 hours, you can safely cast this to TIME WITHOUT TIME ZONE with:

  CAST(start_hour - end_hour AS TIME WITHOUT TIME ZONE)
  

  This is safe because the INTERVAL captures the exact duration between the two points in time, even across DST changes.

• START_HOUR < END_HOUR:
  Comparing two TIMESTAMPTZ values is completely safe—PostgreSQL compares them based on their underlying UTC timestamps, so you don't have to worry about timezone or DST inconsistencies.

• START_HOUR + EXPECTED_HOUR:
  If EXPECTED_HOUR represents a duration (e.g., "add 5 hours to the start time"), cast it to an INTERVAL first:

  start_hour + CAST(expected_hour AS INTERVAL)
  

  If EXPECTED_HOUR represents a time of day (e.g., "calculate the time until 05:00 next day"), you'll need to adjust for the date boundary, but TIMESTAMPTZ makes this straightforward. Either way, the operation is safe because you're working with unambiguous time points.

• EXPECTED_HOUR - END_HOUR:
  Same as the first operation—subtract the two TIMESTAMPTZ values to get an INTERVAL, then cast to TIME if needed. This handles DST changes correctly because it's based on actual UTC timestamps.

• EXPECTED_HOUR < '05:00':
  You need to clarify which timezone's 05:00 you're comparing against. For example, if it's the client's local time, convert '05:00' to a TIMESTAMPTZ using the client's timezone and the report date:

  expected_hour < (report_date + TIME '05:00') AT TIME ZONE 'America/New_York'
  

  This ensures you're comparing unambiguous UTC timestamps, so the comparison is safe.

Bonus: DST Handling #

As you noted, TIMESTAMPTZ subtraction automatically handles DST changes. For example, if you have a start time just before a DST fallback (when clocks roll back an hour) and an end time just after, the interval will correctly reflect the actual duration (e.g., 2 hours instead of 1 or 3). Other operations using TIMESTAMPTZ will also account for DST because they're based on UTC, which doesn't have DST.

内容的提问来源于stack exchange，提问作者Luiz