title: Strategy about working with timezones in postgresql
body: <p>I need to work with multiple timezones in my application.</p>
  
  <p>Basically my system generates data and customers from anywhere on the planet can get access to it.</p>
  
  <p>Since my data have an associated timestamp I thought about the following strategy to deal with timezones.</p>
  
  <p>In Postgres:</p>
  
  <ul>
  <li><p>Create my table with a <code>::timestamptz</code> type for my timestamps. Timestamps without timezone will not be allowed as mixing <code>::timestamp</code> and <code>::timestamptz</code> looks like a time bomb in my case.</p></li>
  <li><p>Set my PG server system's time zone to `UTC.</p></li>
  <li><p>Set <code>timezone='UTC'</code> in <code>postgresql.conf</code> (might not be needed if the system's TZ is UTC but I am a bit paranoid, and it cost me nothing).</p></li>
  <li><p>Add the following check when creating my table :</p>
  
  <p>CONSTRAINT timestamp_must_be_utc CHECK (date_part('timezone'::text, "my_timestamp_field") = 0::double precision)</p></li>
  <li><p>Store all my timestamps in UTC</p></li>
  </ul>
  
  <p>On the client side (python + <a href="http://pytz.sourceforge.net/" rel="nofollow noreferrer">pytz</a>)</p>
  
  <ul>
  <li><p>Storing the customer's timezone in his profile, like <code>'America/Los Angeles'</code></p></li>
  <li><p>Sending the timezone information to postgres when querying data so I can get something like <code>SELECT xxxx FROM yyyy WHERE my_ts &gt;= '2012-11-27 19:13:00+01'::timestamptz</code> and let Postgres do the conversion to UTC. Alternatively I could use <a href="http://pytz.sourceforge.net/" rel="nofollow noreferrer">pytz</a> for the conversion but it seems that Postgres will do this job just fine.</p></li>
  <li><p>Converting timestamps according the customer's timezone so I can display data + timestamp correctly.</p></li>
  </ul>
  
  <p>To summarize, I plan to use UTC everywhere for storing and querying data, and just a timezone conversion when displaying data.</p>
  
  <p>I do not have much experience with Postgres and the way it handles timezones. I know how hard it is to deal with dates and times in different timezones (once you have to work with flight schedules you learn the hard way that using UTC is the only reliable way to do date and time calculus) this is why I am asking this question so that postgres users who are more experienced that me can confirm or correct my strategy.</p>
  
  <p>Interesting links:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/a/9576170/1121179">https://stackoverflow.com/a/9576170/1121179</a></li>
  <li><a href="https://stackoverflow.com/a/10462428/1121179">https://stackoverflow.com/a/10462428/1121179</a></li>
  </ul>