> Timezone data is separate information that if not included, is now missing from your data forever unless someone goes and backfills it in. Without the explicit timezone stored, your timestamp could technically be in any timezone as far as the database is concerned.
As the article points out, confusingly, problematically, and despite its name, "TIMESTAMP WITH TIMEZONE" doesn't, in fact, store timezones.
What it does is implicitly convert between UTC (the storage) and zoned timestamps (anything you give it, either explicitly zoned or — if non-zoned – assumed to be in the server's "timezone" setting).
Thankfully TIMESTAMP WITHOUT TIMEZONE doesn't store timezones either (how weird would that be), what it does is discard timezone information entirely on input, storing only the timestamp as-is: if you give it '1932-11-02 10:34:05+08' it's going to store '1932-11-02 10:34:05' having just stripped out the timezone information without doing any adjustment whereas timestamptz will store '1932-11-02 02:34:05', without maintaining any offset either but having adjusted to UTC:
Postgres has no built-in datatype to store an offset timestamp, let alone a properly zoned one. In fact, it has no datatype to store a timezone at all. Though it does provide pg_timezone_names.
As the article points out, confusingly, problematically, and despite its name, "TIMESTAMP WITH TIMEZONE" doesn't, in fact, store timezones.
What it does is implicitly convert between UTC (the storage) and zoned timestamps (anything you give it, either explicitly zoned or — if non-zoned – assumed to be in the server's "timezone" setting).
Thankfully TIMESTAMP WITHOUT TIMEZONE doesn't store timezones either (how weird would that be), what it does is discard timezone information entirely on input, storing only the timestamp as-is: if you give it '1932-11-02 10:34:05+08' it's going to store '1932-11-02 10:34:05' having just stripped out the timezone information without doing any adjustment whereas timestamptz will store '1932-11-02 02:34:05', without maintaining any offset either but having adjusted to UTC:
Postgres has no built-in datatype to store an offset timestamp, let alone a properly zoned one. In fact, it has no datatype to store a timezone at all. Though it does provide pg_timezone_names.