SQL for Distributed Systems

Re:SQL for Distributed Systems by atombender on Wednesday October 13, 2021 @07:38AM (#28850937)

The sentence you quoted does not occur in the article, so I'm not sure about the context. But generally speaking, the problem is that an app might accidentally do:

  db.exec("INSERT INTO values (t) VALUES (?)", now())

and now your timezoneless column t probably contains the local time.

A lot of code (including ORMs) assumes local time zones everywhere. It's simply hard to avoid, and too easy to accidentally insert bad data.

But the above bug does not happen if your column is declared as something like "TIMESTAMP WITH TIME ZONE" (PostgreSQL, BigQuery, etc., or just "TIMESTAMP" in MySQL).

[ Parent | on HN ]

Re:SQL for Distributed Systems by drran on Wednesday October 13, 2021 @10:17AM (#28852376)

Sorry, I clicked on wrong tab, when responding.

It was a sane choice, to use local timezone for dates, in the past, but it's no longer a sane choice in today's world, because a database can be at another continent, while users can be all around the world.

«Timestamp» is somewhat different from «date». Timestamps can be represented by different dates in different time zones or different chronicle. «Local timestamps» with time zone are prone to error when time zone shifts.

[ Parent | on HN ]