title: DATE_SUB and INTERVAL calculation may be wrong
body: <p><em>This may be too localized, but hoping there may be some information out there that I simply haven't been able to find.</em></p>
  
  <p><strong>Background:</strong> We have a system with two separate Java daemons; one that creates data and inserts into a database, and another that grabs the latest sets of that data (no more than 1 hour old), and pushes to the client.</p>
  
  <p><strong>Problem:</strong> The data being grabbed from this table is occasionally greater than 1 hour old. The time difference can be anywhere between 2 hours to several months old.</p>
  
  <p><strong>Current Query:</strong> Obtaining the 1-hour data is a two-step process. First, we "reserve" a set of records by setting a random negative number:</p>
  
  <pre><code>UPDATE tracking_records as target
    JOIN 
      (SELECT tracking_records.id, `set`, unit_id, tracking_records.record_time 
       FROM tracking_records 
       WHERE `set` IS NULL and record_time &gt; DATE_SUB(NOW(), INTERVAL 1 HOUR) 
       ORDER BY record_time DESC LIMIT 48) source 
    ON source.id = target.id
  SET target.`set` = -1371504452;
  </code></pre>
  
  <p><em>Note: The <code>-1371504452</code> is an example value; it is generated randomly in Java. The <code>LIMIT 48</code> remains the same for each query.</em></p>
  
  <p>And then we simply select the columns that contain that random <code>set</code> value.</p>
  
  <p>This is the structure of the <code>tracking_records</code> table:</p>
  
  <pre><code>+-------------+---------------------+
  | Field       | Type                |
  +-------------+---------------------+
  | id          | bigint(20) unsigned |
  | unit_id     | int(11)             |
  | record_time | datetime            |
  | latitude    | int(11)             |
  | longitude   | int(11)             |
  | created_at  | datetime            |
  | updated_at  | datetime            |
  | set         | int(11)             |
  +-------------+---------------------+
  </code></pre>
  
  <p>As you can see, the query should only match records where the <code>set</code> columns is null AND where the record time is at most 1 hour ago.</p>
  
  <p>As mentioned, I detect time differences of a few hours, up to several months (the oldest I have detected so far is <code>2013-09-23 11:01:08</code>). This does not make sense to me, given the time restrictions of the WHERE clause.</p>
  
  <p>We are using mysql version <strong>5.5.29-0ubuntu0.12.04.2</strong></p>
  
  <p><strong>Question:</strong> I am quite confused, and I am wondering if there is a bug, or some other issue that would cause the time calculation to fail so drastically, and randomly. Either that, or there is a problem with the query itself that I am simply not seeing.</p>
  
  <p>Has anyone observed a problem with incorrect time comparisons, the <code>DATE_SUB()</code> function, or the <code>INTERVAL</code> calculation, in this version of MySQL that might explain the anomalous times I'm seeing?</p>