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.?
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)timestamp without time zone
.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.timestamp without time zone
if you want time zone agnostic behavior (e.g., you manage time zones in your application).