title: Group by day from timestamp with multiple tables
body: <p>I have two tables with <code>timestamp</code> columns.</p>
  
  <p>I want to group the result by days. For example: from <code>2014/06/10</code> to <code>2014/06/13</code>. Doesn't matter if there are records between those dates, I want it to group it by days.</p>
  
  <p>My tables:</p>
  
  <p><code>profits</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/Xt4Rv.png" alt="enter image description here"></p>
  
  <p><code>profits_referrals</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/LZO37.png" alt="enter image description here"></p>
  
  <p>The result I want:</p>
  
  <pre><code>╔═════════════╦══════════════╦══════╗
  ║    date     ║  .........   ║  ..  ║
  ╠═════════════╬══════════════╬══════╣
  ║  2014/06/10 ║  .........   ║  ..  ║
  ║  2014/06/11 ║  .........   ║  ..  ║
  ║  2014/06/12 ║  .........   ║  ..  ║
  ║  2014/06/13 ║  .........   ║  ..  ║
  ╚═════════════╩══════════════╩══════╝
  </code></pre>
  
  <p>A note: also if there are no records from some date, I still want it to show that date, and the <code>amount</code> will be <code>0</code>.</p>
  
  <p>What I did so far:</p>
  
  <pre><code>SELECT      SUM(`profits`.`amount`) AS `profAmount`,
              COUNT(`profits`.`amount`) AS `profCount`,
  
              SUM(`profits_referrals`.`amount`) AS `refAmount`,
              COUNT(`profits_referrals`.`amount`) AS `refCount`,
  
              DATE(FROM_UNIXTIME(`profits`.`date`)) AS `profDate`,
              DATE(FROM_UNIXTIME(`profits_referrals`.`date`)) AS `refDate`
  FROM        `profits`
  JOIN        `profits_referrals`
  ON          `profits`.`userid` = `profits_referrals`.`referral`
  WHERE       `profits`.`userid` = " . (int)$user-&gt;id . "
  GROUP BY    DATE(FROM_UNIXTIME(`profits`.`date`)), DATE(FROM_UNIXTIME(`profits_referrals`.`date`))
  ORDER BY    `profDate`
  DESC
  </code></pre>
  
  <p>Result: (I did some PHP code to display it)</p>
  
  <pre><code>Date              Sales              Referrals      Total
  2014-04-28    2 / $7.35 USD     2 / $1.4 USD    $8.75 USD
  2014-04-28    2 / $7.35 USD     2 / $1.4 USD    $8.75 USD
  2014-03-27    1 / $2.10 USD     1 / $0.7 USD    $2.80 USD
  2014-03-27    1 / $2.10 USD     1 / $0.7 USD    $2.80 USD
  2014-03-25    3 / $6.30 USD     3 / $2.0 USD    $8.40 USD
  2014-03-25    3 / $6.30 USD     3 / $2.0 USD    $8.40 USD
  </code></pre>