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

[vincentdephily]

On Tuesday 23 July 2013 11:45:09 pnorman wrote:
> > 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

The server just replies in a "YYYY/MM/DD HH:MM:SS.nnn" format that has no
concept of a timezone. It is the client who attaches an implicit timezone
(certainly its local timezone) to that value.

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

A cleaner option is to use the [AT TIME ZONE](http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT) construct when running an sql query:

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

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

    tokill=# show time zone;
     Europe/Paris
    tokill=# \d foo
     d_notz | timestamp without time zone | 
     d_tz   | timestamp with time zone    | 
    tokill=# insert into foo values ('2013/01/01 00:00:00', '2013/01/01 00:00:00'),('2013/01/01 00:00:00', '2013/01/01 00:00:00' at time zone 'utc');
    INSERT 0 2
    tokill=# select d_notz, d_notz at time zone 'utc', d_tz from foo;
     2013-01-01 00:00:00 | 2013-01-01 01:00:00+01 | 2013-01-01 00:00:00+01
     2013-01-01 00:00:00 | 2013-01-01 01:00:00+01 | 2012-12-31 23:00:00+01

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