postgresqltimetimezonetimestamp-with-timezone

Editing "parts" of a TIMESTAMPTZ value

I have a TIMESTAMPTZ column in a Postgresql database. To my understanding, Postgresql does not preserve the timezone within a TIMESTAMPTZ column, and instead converts the given timestamp to an absolute UTC value.

Currently, we allow our users to update parts of a timestamp such as the month, day, and timezone. Is it possible to update parts of a pre-existing TIMESTAMPTZ value such as the month, day, and timezone without having to store an IANA timezone value in a separate column?

Currently, I believe that user edits may produce inconsistent results, since for example, the user might pass in a month and expect it to be evaluated in the timezone that they originally passed, not within the context of a UTC timestamp.

Solution

  • The data type timestamp with time zone (timestamptz) stores normalized UTC time internally. No time zone or offset of any kind is stored with it. Hence it fits in the same 8 bytes as timestamp without time zone (timestamp). The time zone of the current session serves as input modifier / output decorator where no explicit time zone is given.

    Is it possible to update parts of a pre-existing TIMESTAMPTZ value such as the month, day, and timezone without having to store an IANA timezone value in a separate column?

    Yes and no.

    No, because month, day and timezone are not "part" of a timestamptz at all. Think of it: the same point in time can translate to a different month or day depending on where in the world you ask. (And we ruled out time zone to begin with.)

    Yes, because you can project the given point in time to any time zone and do all of the above anyway. The result is then stored as timestamptz again. That's actually common practice. And most conversions are done implicitly, often without users being aware of any of this. Just get your syntax right and beware of possible corner cases when crossing DST boundaries.

    If you positively need the input time zone, store it additionally.

    Example

    User A inserts UTC time '2025-06-01 00:10:00 in Vienna, Austria (session time zone 'Europe/Vienna'), using the following timestamptz literal as input:

    '2025-06-01 00:10:00+00'

    Gets the display:

    '2025-06-01 02:10:00+02'  -- different *display*, identical tstz value

    User B sits in New York (session time zone 'America/New_York') and sees:

    '2025-05-31 20:10:00-04'  -- different *display*, identical tstz value

    Doesn't meant users can't "change the month" (relative to their local time). You just need to be aware of what you are doing.

    test=> SELECT timestamptz '2025-06-01 00:10:00+00' AS org                             -- start value
test->      , timestamptz '2025-06-01 00:10:00+00' + interval '1 month' AS plus_1mon  -- add a month
test->      , timestamptz '2025-06-01 00:10:00+00' + interval '1 month' AS plus_1day  -- add a day
test->        -- take local timestamp from Vienna and apply it in NY ("update time zone") ...
test->        -- ... effectively adding 6h since NY time lags by 6h at this time of the year (with DST time adjustments)
test->      , timestamptz '2025-06-01 00:10:00+00' AT TIME ZONE 'Europe/Vienna' AT TIME ZONE 'America/New_York' AS change_tz
test->      ;
          org           |       plus_1mon        |       plus_1day        |       change_tz        
------------------------+------------------------+------------------------+------------------------
 2025-06-01 02:10:00+02 | 2025-07-01 02:10:00+02 | 2025-07-01 02:10:00+02 | 2025-06-01 08:10:00+02

    That's what's displayed for me (session time zone 'Europe/Vienna').
    If you do the same in NY (session time zone 'America/New_York'):

    test=> SET timezone = 'America/New_York';
SET
test=> SELECT timestamptz '2025-06-01 00:10:00+00' AS org                             -- start value
     , timestamptz '2025-06-01 00:10:00+00' + interval '1 month' AS plus_1mon  -- add a month
     , timestamptz '2025-06-01 00:10:00+00' + interval '1 month' AS plus_1day  -- add a day
       -- take local timestamp from Vienna and apply it in NY ("update time zone") ...
       -- ... effectively adding 6h since NY time lags by 6h at this time of the year (with DST time adjustments)
     , timestamptz '2025-06-01 00:10:00+00' AT TIME ZONE 'Europe/Vienna' AT TIME ZONE 'America/New_York' AS change_tz
     ;
          org           |       plus_1mon        |       plus_1day        |       change_tz        
------------------------+------------------------+------------------------+------------------------
 2025-05-31 20:10:00-04 | 2025-06-30 20:10:00-04 | 2025-06-30 20:10:00-04 | 2025-06-01 02:10:00-04

    Related: