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

[pnorman]

> The issue may be something along the lines that Postgres (or the client library) is assuming "without timezone" means "in local timezone"

I believe that's what without timezone is defined as in the SQL standard

>  A simple work-around may be to set $TZ when running Osmosis.

I also need to try inserting by hand to see what results I get

> Anecdotally, my experience of writing systems with timestamp information is that it's always easier to keep data internally in UTC at all times and only convert when presenting data to the user. In OSM, I don't think we ever convert out of UTC - all API-generated timestamps should be UTC, and uploaded timestamps are ignored.

Well, the in-database timestamps aren't in UTC because they're not in any timezone.

> In my opinion, timestamp and timezone information are qualitatively very different: the timestamp indicates a particular point in time which is unambiguous to a computer, whereas timezones are only useful as a presentation mechanism to a human. The prevalence of time_t in POSIX APIs would seem to indicate that the designers shared this opinion

But in SQL a timestamp without timezone is ambiguous to a computer because it could be in any time zone. When I first read it awhile back the SQL definitions seemed flipped from what I expected in some cases.

> @pnorman it is a substantial change if only because it requires an "ALTER COLUMN" to be performed on many of our largest tables

Good point, the time required may be non-trivial with the table rewrite, even if the change itself is simple.

---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-21436635
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20130723/4836673e/attachment.html>