`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.
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?
First off, it's regrettable that Postgres drivers are parsing strings at all. That opens this can of worms.
Secondly, the DB locale setting is weird. It's advisable to always set your Postgres config to UTC locale to eliminate 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.
PST database:
select now() as timestamptz; -- 2022-11-09T14:41:12.110-08
select now() as timestamp; -- 2022-11-09T14:41:12.110
UTC database:
select now() as timestamptz; -- 2022-11-09T22:41:12.110Z, "Z" means UTC
select now() as timestamp; -- 2022-11-09T22:41:12.110
This isn't clearly 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. Which makes this even more of a landmine if you ever use a DB not configured this way.
Another moral of the story is, every string representation of a datetime has a time zone, so it's better to be explicit about it. Something like "2022-11-09T14:41:12.110" is ambiguous. Put the "+00" or the "Z" if you mean UTC. Unix timestamps, on the other hand, are simply defined as a duration of time that has passed since the epoch and have no concept of time zone (don't even call them UTC).
tl;dr Just say no to `timestamp`