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

[Andy Allan]

> The "Z" (zulu) character in the timestamp string implies that all timestamps should be returned in UTC-0, no matter what time zone the server runs in.

That's not quite what it's doing. It's taking the timestamp from the database, formatting it appropriately and then sticking an actual letter Z at the end of the string (like the T, it's double quoted - it's not a formatting character like YYYY so postgres is just adding that character to the string). So there's no timezone conversion happening in the to_char method. It's taking the `timestamp without timezone` and making it look like a UTC timestamp. Which of course works fine if the server is in UTC but leads to other problems in other situations.

> This ended up creating wrong timestamps all over the place.

Yes, this is the same problem. Osmosis will have read the UTC timestamp from the XML file, handled it internally as UTC all through the pipelines, and then when writing it to the database the pg connection library will have converted that time to local time when storing it as `timestamp without timezone`. Then cgimap / rails port / whatever will have used that timestamp, assumed it was UTC and there's the problem.

-- 
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-440690343
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20181121/9f3db98d/attachment.html>