title: Dealing with timezones in PHP
body: <p>Some issues with timezones in PHP have been in the back of my mind for a while now, and I was wondering if there are better ways to handle it than what I'm currently doing.</p>
  
  <p>All of the issues revolve around reformating database stored dates:</p>
  
  <p>When dealing with a site that has to support multiple timezones (for users), to normalize the timezone offest of stored timestamps I always store it with the server timezone using the <code>CURRENT_TIMESTAMP</code> attribute or the <code>NOW()</code> function. </p>
  
  <p>This way I don't have to consider what timezone was set for PHP when the timestamp was entered (since PHP time functions are timezone aware). For each user, according to his preference I set the timezone somewhere in my bootstrap file using:</p>
  
  <pre><code>date_default_timezone_set($timezone);
  </code></pre>
  
  <p>When I'm looking to format dates with the php <code>date()</code> function, some form of conversion has to take place since MySQL currently stores timestamp in the format <code>Y-m-d H:i:s</code>. With no regard to timezone, you could simply run:</p>
  
  <pre><code>$date = date($format,strtotime($dbTimestamp));
  </code></pre>
  
  <p>The problem with this is that <code>date()</code> and <code>strtotime()</code> are both timezone aware functions, meaning that if the PHP timezone is set differently from the server timezone, the timezone offset will apply twice (instead of once as we would like).</p>
  
  <p>To deal with this, I usually retrieve MySQL timestamps using the <code>UNIX_TIMESTAMP()</code> function which is not timezone aware, allowing my to apply <code>date()</code> directly on it - thereby applying the timezone offset only once.</p>
  
  <p>I don't really like this 'hack' as I can no longer retrieve those columns as I normally would, or use <code>*</code> to fetch all columns (sometimes it simplifies queries greatly). Also, sometimes it's simply not an option to use <code>UNIX_TIMESTAMP()</code> (especially when using with open-source packages without much abstraction for query composition).</p>
  
  <p>Another issue is when storing the timestamp, when usage of <code>CURRENT_TIMESTAMP</code> or <code>NOW()</code> is not an option - storing a PHP generated timestamp will store it with the timezone offset which I would like to avoid.</p>
  
  <p>I'm probably missing something really basic here, but so far I haven't been able to come up with a generic solution to handle those issues so I'm forced to treat them case-by-case. Your thoughts are very welcome</p>