title: Better way to safely insert UTC time value into mySQL TIMESTAMP variable?
body: <p>My application wants to insert a timestamp value into a TIMESTAMP variable in a mySQL database.  The timestamp value is a UTC time in the usual "YYYY-MM-DD HH:MM:SS" format.  The problem is that my SQL server is set to use SYSTEM time (<code>SELECT @@global.time_zone</code> says <code>SYSTEM</code>), and the system timezone is Europe/London (the server is running Ubuntu 14.04), so mySQL does a daylight-saving conversion and stores the value one hour off from what it ought to be.  (I guess if I was in another timezone e.g. CST then I'd have an unwanted timezone offset as well as the daylight saving hour).</p>
  
  <p>To get mySQL to do The Right Thing it appears that I need to covert the UTC timestamp into system time before I insert it so that mySQL can convert it from system time to UTC time before it stores it internally.  This bit of code has the desired effect:-</p>
  
  <pre><code>mysql&gt; select timestamp("2015-05-06 12:34:56")+CURRENT_TIMESTAMP-UTC_TIMESTAMP;
  </code></pre>
  
  <pre class="lang-none prettyprint-override"><code>+------------------------------------------------------------------+
  | timestamp("2015-05-06 12:34:56")+CURRENT_TIMESTAMP-UTC_TIMESTAMP |
  +------------------------------------------------------------------+
  |                                            20150506133456.000000 |
  +------------------------------------------------------------------+
  </code></pre>
  
  <p>I'm going to run with this for now, but it seems like a bit of a palaver, so is there a better way?</p>
  
  <p>[edit] Another round of RTFM'ing gave me this idea...</p>
  
  <pre><code>mysql&gt; select CONVERT_TZ("2015-05-06 12:34:56", "+00:00", "SYSTEM");
  </code></pre>
  
  <pre class="lang-none prettyprint-override"><code>+-------------------------------------------------------+
  | CONVERT_TZ("2015-05-06 12:34:56", "+00:00", "SYSTEM") |
  +-------------------------------------------------------+
  | 2015-05-06 13:34:56                                   |
  +-------------------------------------------------------+
  </code></pre>
  
  <p>That looks a lot  cleaner.  Any better ideas?</p>