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

[vincentdephily]

>> 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".
> The server's timestamps are actually in UTC. Therefore it seems the cleanest solution is fixing the bugs in the client where they are assuming, incorrectly, that they're in local time.

The 'with timezone' variant is the idiomatic way to fix timezone issues. It was created for this purpose and is supported all the way down to the wirelevel protocol. You can fix timezone issues another way, but that's IMHO just a hack.

The 'without timezone' variants aren't lighter than the 'with' ones; they exist mainly for sql compliance / backward compatibility and there's no good reason to use them nowadays.

> ```sql
>     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
> ```
> This looks to me like the d_notz column is exactly what we want: Put the data in and take it out without the server messing with it. We just have to stop the client messing with it also.

You're hoping to outsmart the postgres devs, it's rarely a good bet :p You can either fix the timezone issue once server-side following best-practice, or multiple times (once per client) using one of the solutions above.

That being said, if the "alter table" downtime is unacceptable to the sysadmins, then that theoretical discussion is moot, and the practical decision to fix things client-side wins.

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