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

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

<blockquote>
<blockquote>
<p>I also need to try inserting by hand to see what results I get</p>
</blockquote>

<div class="highlight"><pre>    <span class="n">tokill</span><span class="o">=#</span> <span class="k">show</span> <span class="n">time</span> <span class="k">zone</span><span class="p">;</span>
     <span class="n">Europe</span><span class="o">/</span><span class="n">Paris</span>
    <span class="n">tokill</span><span class="o">=#</span> <span class="err">\</span><span class="n">d</span> <span class="n">foo</span>
     <span class="n">d_notz</span> <span class="o">|</span> <span class="k">timestamp</span> <span class="k">without</span> <span class="n">time</span> <span class="k">zone</span> <span class="o">|</span> 
     <span class="n">d_tz</span>   <span class="o">|</span> <span class="k">timestamp</span> <span class="k">with</span> <span class="n">time</span> <span class="k">zone</span>    <span class="o">|</span> 
    <span class="n">tokill</span><span class="o">=#</span> <span class="k">insert</span> <span class="k">into</span> <span class="n">foo</span> <span class="k">values</span> <span class="p">(</span><span class="s1">'2013/01/01 00:00:00'</span><span class="p">,</span> <span class="s1">'2013/01/01 00:00:00'</span><span class="p">),(</span><span class="s1">'2013/01/01 00:00:00'</span><span class="p">,</span> <span class="s1">'2013/01/01 00:00:00'</span> <span class="k">at</span> <span class="n">time</span> <span class="k">zone</span> <span class="s1">'utc'</span><span class="p">);</span>
    <span class="k">INSERT</span> <span class="mi">0</span> <span class="mi">2</span>
    <span class="n">tokill</span><span class="o">=#</span> <span class="k">select</span> <span class="n">d_notz</span><span class="p">,</span> <span class="n">d_notz</span> <span class="k">at</span> <span class="n">time</span> <span class="k">zone</span> <span class="s1">'utc'</span><span class="p">,</span> <span class="n">d_tz</span> <span class="k">from</span> <span class="n">foo</span><span class="p">;</span>
     <span class="mi">2013</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span> <span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">2013</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span> <span class="mi">01</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="o">+</span><span class="mi">01</span> <span class="o">|</span> <span class="mi">2013</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span> <span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="o">+</span><span class="mi">01</span>
     <span class="mi">2013</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span> <span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">2013</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span> <span class="mi">01</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="o">+</span><span class="mi">01</span> <span class="o">|</span> <span class="mi">2012</span><span class="o">-</span><span class="mi">12</span><span class="o">-</span><span class="mi">31</span> <span class="mi">23</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="o">+</span><span class="mi">01</span>
</pre></div>
</blockquote>

<p>This looks to me like the <code>d_notz</code> 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.</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br>Reply to this email directly or <a href='https://github.com/openstreetmap/openstreetmap-website/issues/375#issuecomment-21479654'>view it on GitHub</a>.<img src='https://github.com/notifications/beacon/uTRSc6ihLa7Shf84BpiOpu5p-sEO603Adi0xI76mcGfT_gJWm4GdBKeaxLg8tieb.gif' height='1' width='1'></p>