<blockquote>
<blockquote>
<p>The issue may be something along the lines that Postgres (or the client<br>
library) is assuming "without timezone" means "in local timezone"</p>
</blockquote>

<p>I believe that's what without timezone is defined as in the SQL standard</p>
</blockquote>

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

<blockquote>
<blockquote>
<p>A simple work-around may be to set $TZ when running Osmosis.</p>
</blockquote>
</blockquote>

<p>That's a good quickfix, but means that the whole of osmosis will run with<br>
the server's timezone, which might be overkill.</p>

<p>A cleaner option is to use the <a href="http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT">AT TIME ZONE</a> construct when running an sql query:</p>

<div class="highlight"><pre>    <span class="k">SELECT</span> <span class="nb">date</span> <span class="k">AT</span> <span class="n">TIME</span> <span class="k">ZONE</span> <span class="s1">'utc'</span> <span class="k">from</span> <span class="n">current_foobar</span><span class="p">;</span>
</pre></div>

<p>This will tell PG that the 'without timezone' value is stored in UTC. The<br>
server can then check the client's timezone setting, and send the value in<br>
the client's expected timezone. It works similarly for inserts. This has<br>
the advantage that a regional osm website can continue to use its local<br>
timezone for user-facing timestamps without getting confused the the server's<br>
timezone.</p>

<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>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>
<span class="o">```</span><span class="k">sql</span>
</pre></div>

<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-21479273'>view it on GitHub</a>.<img src='https://github.com/notifications/beacon/uTRSc6ihLa7Shf84BpiOpu5p-sEO603Adi0xI76mcGfT_gJWm4GdBKeaxLg8tieb.gif' height='1' width='1'></p>