[openstreetmap/openstreetmap-website] Switch to TIMESTAMP WITH TIME ZONE for current_* tables (#375)

[Andy Allan]

This issue has come up again in #2028 and I feel it's something that we need to resolve. Unfortunately there's a few misunderstandings from @tomhughes and @zerebubuth on this topic, so I hope to persuade them of the case for changing the column type.

http://www.postgresqltutorial.com/postgresql-timestamp/ has some background reading.

The main misunderstanding appears to be that `timestamp without timezone` means UTC and `timestamp with timezone` stores a timestamp with an additional timezone offset. **This is not the case**. Both columns store the same information, i.e. a timestamp. Same bytes. Same timestamp. The difference is only how software should interpret those bytes.

* `timestamp without timezone` - "2018/11/21 10:05" is an event that can happen [38 times](https://www.timeanddate.com/time/current-number-time-zones.html), once in each timezone throughout the day. *"without timezone" means "treat as local time"* and so if you connect to the database with a client in UTC+8, the timestamp refers to "2018-11-21T10:05Z+08:00". This is absolutely not what we want, so everything that connects to our database has to be **very** careful to interpret the timestamp as UTC - and we end up jumping through hoops as described above.
* `timestamp with timezone` - "2018/11/21 10:05" is an event that happens exactly once, at 10:05 UTC. Let me emphasise again that this column type does **not** store a timezone offset. It is an indication that the timestamp is, and always is, UTC. This is what we want.

The implications of the change for OSMF are low, since they run all their servers in UTC. But the implications for anyone else running the software, for example a developer on a laptop in another timezone, is that they are likely to get a messed up timestamps in their db. Values that should be UTC (e.g in a dump) are stored as local times instead. Changing the column type to properly reflect that the timestamps are UTC and not local time will remove a whole lot of head-scratching.

So to summarise, we are currently using `timestamp without timezone` (aka "this timestamp should be interpreted as local time") and then jumping through hoops to make every client connect as UTC. A simple column type change to `timestamp with timezone` (aka "this timestamp is a UTC timestamp") would be the right thing to do.

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-440620185
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20181121/61e39c84/attachment-0001.html>