Hacker News new | ask | show | jobs
by Too 1322 days ago
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?
1 comments
hot_gril 1318 days ago
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).

tl;dr Just say no to `timestamp`

link