title: Retrieving UTC DATETIME field from MySQL in Java when server timezone is not UTC
body: <p>I'm trying to write code to interoperate with a third-party-developed database using Java and MySQL.  This database has a field that stores a time stamp in a <code>DATETIME</code> field as a UTC date.  The timezone for the server on which both the database and client run is set to a non-UTC zone (<code>Europe/London</code>), so by default the timestamp is read back incorrectly as if it were a local time.  I'm trying to write code to read it back as UTC.</p>
  
  <p>I have read several similar questions here, but none of them have an answer that works for me:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/13235191/mysql-how-to-store-time-with-correct-timezone-from-java">MySQL - how to store time with correct timezone? (from Java)</a></li>
  <li><a href="https://stackoverflow.com/questions/309203/how-to-store-a-java-util-date-into-a-mysql-timestamp-field-in-the-utc-gmt-timezo">How to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?</a></li>
  <li><a href="https://stackoverflow.com/questions/6099753/date-in-utc-in-mysql">Date in UTC in mysql</a></li>
  <li><a href="https://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql">How do I set the time zone of MySQL?</a></li>
  </ul>
  
  <p>Unfortunately, I cannot change any server settings, so I have tried using the connection's "time_zone" variable to set the database server to use UTC and the optional <code>Calendar</code> parameter to <code>ResultSet.getTimestamp</code> to retrieve the date, but this has no effect on the result.  Here is my code:</p>
  
  <pre><code>private static final Calendar UTCCALENDAR = Calendar.getInstance (TimeZone.getTimeZone (ZoneOffset.UTC));
  public Date getDate ()
  {
      try (Connection c = dataSource.getConnection ();
           PreparedStatement s = c
               .prepareStatement ("select datefield from dbmail_datefield where physmessage_id=?"))
      {
          fixTimeZone (c);
          s.setLong (1, getPhysId ());
          try (ResultSet rs = s.executeQuery ())
          {
              if (!rs.next ()) return null;
              return new Date (rs.getTimestamp(1,UTCCALENDAR).getTime ());    // do not use SQL timestamp object, as it fucks up comparisons!
          }
      }
      catch (SQLException e)
      {
          throw new MailAccessException ("Error accessing dbmail database", e);
      }
  }
  
  private void fixTimeZone (Connection c)
  {
      try (Statement s = c.createStatement ())
      {
          s.executeUpdate ("set time_zone='+00:00'");
      }
      catch (SQLException e)
      {
          throw new MailAccessException ("Unable to set SQL connection time zone to UTC", e);
      }
  }
  </code></pre>
  
  <p>The database field I'm trying to read has a value stored in it as follows:</p>
  
  <pre><code>mysql&gt; select * from dbmail_datefield where physmessage_id=494539;
  +----------------+--------+---------------------+
  | physmessage_id | id     | datefield           |
  +----------------+--------+---------------------+
  |         494539 | 494520 | 2015-04-16 10:30:30 |
  +----------------+--------+---------------------+
  </code></pre>
  
  <p>But unfortunately, the result comes out as BST not UTC:</p>
  
  <pre><code>java.lang.AssertionError: expected:&lt;Thu Apr 16 11:30:30 BST 2015&gt; but was:&lt;Thu Apr 16 10:30:30 BST 2015&gt;
  </code></pre>