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

mmd notifications at github.com
Mon Nov 26 15:03:19 UTC 2018


>  it appears to work out of the box. This is because the clients you list all assume that the data stored in the 'timestamp' columns is UTC, when it is actually 'local time'. Since the assumption is symmetric (i.e. the same mistake on writing and reading), you don't see the problem



Yes, that's pretty much in line with http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/

_The answer is semantics. Whenever you encounter a timestamptz, you know it denotes absolute time. It was stored in UTC. However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking at it. It is merely a wall time. Maybe it’s UTC, or maybe the developer stored it in local time. Without additional context, you can’t be sure._

What we rely on here is the additional context.

> Basically we should try to come up with a solution that allows cgimap to work with both timestamp and timestampz columns interchangeably.

Right, maybe we could add something in the code to add an `SET timezone TO 'UTC';` if required, e.g. based on some table metadata (timezone without vs. with timezone). Ideally, this would avoid changing all SQL statements where this is an issue.

I'm not exactly sure if this is what @pnorman proposed earlier, it sounded more like he wanted to control this from the outside via some environment parameter, which I don't find an appealing solution.



-- 
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-441669899
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20181126/8cf2eba5/attachment-0001.html>


More information about the rails-dev mailing list