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

[pnorman]

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

via #postgresql, while thinking about pnorman/openstreetmap-api-testsuite#1
```
22:02 < RhodiumToad> easy then - timestamp with time zone has, in pg, exactly the same semantics as time_t
22:02 < RhodiumToad> (except for more range and resolution)
22:04 < pnorman> the main API software which is the main user of this database schema is actually written in ruby in rails, but I strongly suspect no code changes are needed except to update the schema
22:05 < RhodiumToad> iirc rails these days does have the option to behave sanely with timestamp with time zone
22:05 < RhodiumToad> but I don't use it myself, that's just from helping other people with it
```

-----



> > A simple work-around may be to set $TZ when running Osmosis.
> That's a good quickfix, but means that the whole of osmosis will run with
the server's timezone, which might be overkill.

Yes - I think it would screw up progress displays at the least

> A cleaner option is to use the AT TIME ZONE construct when running an sql query:

> ```sql
SELECT date AT TIME ZONE 'utc' from current_foobar;
```
> This will tell PG that the 'without timezone' value is stored in UTC. The
server can then check the client's timezone setting, and send the value in
the client's expected timezone. It works similarly for inserts. This has
the advantage that a regional osm website can continue to use its local
timezone for user-facing timestamps without getting confused the the server's
timezone.

> It's not as clean a solution as altering the db schema (because it requires
the client to modify its sql queries and it requires the client code to know
what timezone the server's timestamps actualy are in), but it avoid the
lenghty "alter table".

Well, the output OSM XML is all in UTC in the format ``2012-09-30T17:00:00Z``

For cgimap how it gets this is it converts to a string in postgresql, see https://github.com/zerebubuth/openstreetmap-cgimap/blob/8bfff6/src/backend/apidb/writeable_pgsql_selection.cpp#L399 which does 
```sql
to_char(n.timestamp,'YYYY-MM-DD"T"HH24:MI:SS"Z"')
```

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