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.