`timestamptz` stores the same data I believe. Only that when using `timestamptz` instead of `timestamp`, the server will convert the returned value based on the server time zone configuration. Both datatypes have 8 bytes.

But normally you have a server between the user and the db, where you also prefer to keeps things as utc, while in memory. Then conversion to local time happens as the very last step in the presentation layer.

So unless you are using the sql result verbatim as presentation layer, is there any big benefit of this? On the contrary it seems like yet another moving part one could mess up and accidentally convert time where it shouldn’t?

It's not what it looks like. Selecting either timestamp or timestamptz gives you a string representation in the database's configured local time zone, which your backend's Postgres driver will convert to whatever date representation (like `Date` in NodeJS). But timestamp leaves the time zone out of the string. Your Postgres driver assumes UTC in that case, which is a problem if your database is set to PST.

So with a PST database...

select now() as timestamptz; -- 2022-11-09T14:41:12.110+08

select now() as timestamp; -- 2022-11-09T14:41:12.110

You're right, this shouldn't be a thing. It's advisable to always set your Postgres config to UTC locale to eliminate the root of this nonsense, but that's not the default. So if your database is on PST, at least timestamptz will output strings that tell your code the correct time zone.

This is poorly explained in the official docs, btw. I think the only thing saving a lot of users is how AWS, GCP, etc all set their Postgres instances to UTC by default.

It converts the value either way. Only with timestamptz, the converted value includes the time zone.

Per the docs[0], TIMESTAMP WITH TIME ZONE is preferred because it will automatically convert to/from the stored UTC to whatever your time zone is set to, in the config (by default) or within a query.

I think you also need it for converting input at specified time zones, which is more complicated than one might hope, using AT TIME ZONE.

So the point is about conversion, which is important, but I agree that it’s non-obvious that you need to store the source time zone separately if you ever want to retrieve it.

[0]: https://www.postgresql.org/docs/current/datatype-datetime.ht...