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 probably assumes UTC in that case, which is a problem if your database is set to PST.
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).
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`