> stuck with a column with a timestamp and another column with a timezone.

I've been tinkering with a weird hack for this issue which might help or at least offer some inspiration.

It's similar to the foot-gun of "eagerly convert straight to UTC" except you can easily recalculate it later whenever you feel like it. Meanwhile, you get to keep the same performance benefits from all-UTC sorting and diffing.

The trick involves two additional columns along with time_zone and time_stamp, like:

    -- Column that exist as a kind of trigger/info
    time_recomputed_on TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),

    -- You might be able to do this with special triggers too
    -- This coalesce() is a hack so that the above column changing causes re-generation
    estimated_utc TIMESTAMP GENERATED ALWAYS AS (COALESCE(timezone('UTC', timezone(time_zone, time_stamp)), time_recomputed_on)) STORED

PostgreSQL will recalculate estimated_utc whenever any of the other referenced columns change, including the useless dependency on time_recomputed_on. So you can force a recalc with:

    UPDATE table_name SET time_recomputed_on = now() WHERE time_recomputed_on < X;

Note that if you want time_recomputed_on to be more truthful, it should probably get updated if/when either time_zone or stamp are changed. Otherwise it might show the value as staler than it really is.

https://www.postgresql.org/docs/current/ddl-generated-column...