2

I have a table with a "timestamp with time zone" column.

For one row, it had this value: 2021-06-24 00:00:00+02.

Then, I edited and saved it to be: 2021-06-24 11:00:00-05. Note that both the hour and the timezone has changed.

When I then re-retrieved the value, it was now: 2021-06-24 18:00:00+02.

In other words, it seems to "prefer" the timezone +02 and changes any updated values to be in that timezone, even though I changed the timezone to -05 (because that's the timezone Microsoft gave for their live event). That data is apparently "lost"; seen as unimportant.

Of course, the time is not wrong now, but it's changed in the sense that it isn't explicitly what I changed it to -- just the local timezone version of that time.

This confuses me. What is the reason it was designed like this? Wouldn't it be good to retain the timezone and inputted time for each field? Is it hidden/stored somewhere? If I really want that to be stored, do I have to manually make my own columns and handle this manually? For example, if I want to display something in my control panel that points out that this timestamp origins from this or that timezone, etc.?

Improve this question
4
  • 3
    It is crucial to understand that the timestamptz type (timestamp with timezone) does NOT store timezone data, it stores everything in UTC (and then coverts to whatever you want or settings you have for output)
    – ypercubeᵀᴹ
    Jun 23, 2021 at 11:41
  • @ypercubeᵀᴹ Note that I'm not talking about output, but only how it's stored. It's confusing since there is also a timestamp without time zone.
    – Rockerduck
    Jun 23, 2021 at 12:19
  • 1
    I know you are not talking about output. Timestamptz does not store timezone, that's the thing to remember (and never use plain timestamp). If you want to store the original timezone (or the time offset), you'll need to add another column in the table, along with the timestamptz.
    – ypercubeᵀᴹ
    Jun 23, 2021 at 12:36
  • @ypercubeᵀᴹ There is nothing wrong with timestamp without time zone if you want time zone agnostic behavior (e.g., you manage time zones in your application).
    – Laurenz Albe
    Jun 24, 2021 at 2:03

1 Answer 1

Reset to default
3

timestamp with time zone does not quite meet the expectations of the SQL standard. It should more appropriately be called “absolute timestamp”.

It is stored as microseconds since 2000-01-01 00:00:00 UTC, and when converted to a string, it is converted to the time zone indicated by the current setting of the timezone parameter in your session.

If you need to retain the original time zone, you will have to store it in an additional column.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.