title: MySQL join two tables where date between two dates
body: <p>I'm having a table that contains 6 columns: id (PK, A_I), systemID (varchar(32)), total (varchar(32)), difference (varchar(16)), updated (datetime), error (int).
  The table is updated every hour and right now there are over 2 million records. Therefore I want to split the table up into one containing just todays values and one containing historical data. Every day at midnight data from todays table is moved to historical. So far so good.</p>
  
  <p>Problem is that the user has the option to view the difference values in day view, week view, month view and year view. I don't know how to join the today table with the historical table, so they act as one. Presently the query for week view is:</p>
  
  <pre><code>SELECT difference FROM productionlog 
  WHERE systemID = '$id'
  AND DATE(updated) BETWEEN '$weekStart' AND '$weekEnd'
  ORDER BY updated
  </code></pre>
  
  <p>How do I join the today table with the historical table and achieve the above result?</p>