title: Mysql converting TIMESTAMP to INTEGER - timezones
body: <p>I need to convert some TIMESTAMP fields to INT in our MySQL (InnoDB) DB. I realize that converting a TIMESTAMP to INT is unusual, but we still need to do it :) </p>
  
  <p>It seems straight-forward enough to do, but there are some timezone and daylight saving errors.</p>
  
  <p>I have a script that generates my SQL code per column. For example, it generates:</p>
  
  <pre><code>ALTER TABLE alarmLog ADD COLUMN started_tmp INT UNSIGNED;
  UPDATE alarmLog SET started_tmp = UNIX_TIMESTAMP(started);
  ALTER TABLE alarmLog DROP started;
  alter TABLE alarmLog CHANGE started_tmp started INT UNSIGNED NULL DEFAULT 0;
  </code></pre>
  
  <p>If I compare the before and after data using <code>select FROM_UNIXTIME(1291788036);</code>, the result looks good.</p>
  
  <p>The idea is then to change all the client-side software to convert to UTC and use that INT when storing it. When retrieving, that INT is converted to the current time zone.</p>
  
  <p>But then the docs <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp" rel="noreferrer">warn me</a> about this scenario (daylight savings in CET):</p>
  
  <pre><code>mysql&gt; SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
  +---------------------------------------+
  | UNIX_TIMESTAMP('2005-03-27 02:00:00') |
  +---------------------------------------+
  |                            1111885200 |
  +---------------------------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
  +---------------------------------------+
  | UNIX_TIMESTAMP('2005-03-27 03:00:00') |
  +---------------------------------------+
  |                            1111885200 |
  +---------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>How do API and OS's normally deal with daylight savings? I know my PC has its clock in UTC and in summer time, the OS adds two hours to it, and in winter time one. I assume it uses the UTC time to determine whether it's DST or not.</p>
  
  <p>So, how do I deal with this? Is the only solution to add a field to the database to specify DST offset?</p>