4 ms·
Unfortunately `timestamp with time zone` is misleading and crippled. When you store something as timestamptz, postgres converts it to UTC and "loses" the timez
by emilsedgh 8y ago
Unfortunately `timestamp with time zone` is misleading and crippled.
When you store something as timestamptz, postgres converts it to UTC and "loses" the timezone part.
The only difference between timestamptz and timestamp is that when postgres is returning the timestamptz to you, it converts it back to your local time zone defined by SET TIME ZONE (and not the time zone it was originally stored in)
So basically if you store a timestamptz, you cannot convert it back to the local time, as the local time is never even stored.
I personally had to add a second `timezone` column to my table to store the timezone of the date.
- colanderman 8y agoI wouldn't go so far as to say the timestamp types are broken, just confusingly named. `timestamp with time zone` signifies an absolute point in time; `timestamp without time zone` signifies a timestamp relative to some unspecified time zone (which you must store separately, as you point out). The builtin functions universally adhere to these semantics. E.g., when you do store the timezone separately, use `timestamp without time zone`: `time AT TIME ZONE timezone` [1] only does the right thing (i.e., interpreting `time` as relative to `timezone`, and producing an absolute time corresponding to it) if `time` is `without time zone`. (If `time` is `with time zone`, it produces the local time corresponding to the absolute time `time`, which is mathematically exactly the opposite of what you want.) [1] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT https://www.postgresql.org/docs/current/functions-datetime.h...
- emilsedgh 8y agoI said they are crippled, not broken. Unfortunately the poor naming is a big faux pas. I really really expected timestamp with time zone to store the time zone.