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

[Matt Amos]

>> 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

>> 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.

Indeed - the timestamps aren't in a definite timezone as far as the database is concerned (except that it assumes some for conversions from "with timezone") - my meaning was that we define all timestamps to be in UTC, and always internally work in UTC as if we're using `time_t`.

The [Postgres DATETIME docs](http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#AEN5714) say "a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication." So the next question is whether the _client_ performs any implicit conversion, or passes the information as "with timezone", forcing a conversion.

>> 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.

Sure, which is why it's easier to define that we always only store UTC timestamps, and we don't (shouldn't) perform any conversions in the database or in the database clients. Timezone information, if useful at all, should only be used in the presentation layer to the user.


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