title: Get unix timestamp from mysql database with offset applied
body: <p>I have the offset in seconds to UTC, all timestamps stored on the database are in UTC, how to apply the offset during selection queries,</p>
  
  <pre><code>mysql_query("SELECT * FROM table WHERE unix_timestamp=unix_timestamp with offset applied");
  
  mysql_query("INSERT INTO table (unix_timestamp) VALUES(UNIX_TIMESTAMP())");
  </code></pre>
  
  <p>I'd want to insert in UTC time to the database and retrieve in local time.</p>
  
  <p>On PHP:</p>
  
  <pre><code>date_default_timezone_set('UTC');
  
  $user_timezone_offset=-7200; // -2
  </code></pre>
  
  <p>Could I substract on select's runtime of mysql to the unix_timestamp field $user_timezone_offset and that would be it?</p>
  
  <p>I am inserting in UTC which is what I need apparently, but for selection in user's local time I'd need to apply the offset to the timestamp stored in the database somehow.</p>
  
  <p>Edit: I just came across this:</p>
  
  <pre><code>$user_timezone_offset="-2:00";
  
  FROM_UNIXTIME(CONVERT_TZ(unix_timestamp,'+00:00','$user_timezone_offset'))
  </code></pre>
  
  <p>Would need to know how to apply it to a select query making the selection of unix_timestamp converted for comparison inside the select and the result also becoming modified for php's fetch_array</p>
  
  <p>This seems to be the solution:</p>
  
  <pre><code> SELECT *, Unix_Timestamp(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp), '+00:00', '$user_timezone_offset')) as unix_timestamp
  </code></pre>
  
  <p>Actually this has a problem - although on</p>
  
  <pre><code> mysql_fetch_array($result){ $row['unix_timestamp']; }
  </code></pre>
  
  <p>comes converted while checking on</p>
  
  <pre><code>  WHERE unix_timestamp=value //assuming unix_timestamp
  </code></pre>
  
  <p>to be converted on the select - in there it is not converted and another </p>
  
  <pre><code>  Unix_Timestamp(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp), '+00:00', '$user_timezone_offset')) 
  </code></pre>
  
  <p>has to be called for unix_timestamp to be "converted" each time a check is to be made against the converted value.</p>