title: Generate a sequential date_time by minute in MySQL
body: <p>I'm using the below MySQL found on the web to generate a list of dates.
  However, I want it to generate a list of date_times by minute, E.G yyyy-mm-dd hh:mm:ss.
  Is this possible? I can't wrap my head around how the existing SQL generates the list nevermind adding minutes!</p>
  
  <p>I need this for my base query for a chart / graph, using left join to pull in data from elsewhere. </p>
  
  <p>Thanks</p>
  
  <pre><code>select * from 
  (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
   (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
   (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
   (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
   (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
   (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
  where selected_date between '2019-01-01' and '2019-01-15'
  </code></pre>
  
  <p>Current output:</p>
  
  <pre><code>selected_date
  ---
  2019-01-01
  2019-01-02
  2019-01-03
  2019-01-04
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>selected_date
  ---
  2019-01-19 00:05:00
  2019-01-19 00:06:00
  2019-01-19 00:07:00
  2019-01-19 00:08:00
  2019-01-19 00:09:00
  2019-01-19 00:10:00
  2019-01-19 00:11:00
  </code></pre>
  
  <p>Edit:
  This is my SQL query serving the graph. I get 5-6 temperature records per minute, so I'm averaging them. 
  My problem is sometimes a temperature probe drops off, so a.date_time or b.date_time doesn't have a consecutive date_time for every single minute</p>
  
  <pre><code>    select 
          b.date_time,
          a.temperature as fishtank,
          b.temperature as room
      from (
          select
              serial,
              convert((min(date_time) div 100)*100, datetime)  as date_time, 
              avg(temperature) as temperature
          from
              raspicontroller.temperature
          WHERE 
              date_time between '$date_from' and '$date_to' and 
              serial like '28-000898430d59'
          group by 
              date_time div 100
      ) a
      right join (
          select
              serial,
              convert((min(date_time) div 100)*100, datetime)  as date_time, 
              avg(temperature) as temperature
          from
              raspicontroller.temperature
          WHERE 
              date_time between '$date_from' and '$date_to' and 
              serial like '28-000f9843201e'
          group by 
              date_time div 100
      ) b on a.date_time = b.date_time
  </code></pre>
  
  <p>Edit again:
  I've not managed to generate what I wanted, however I've come up with this which uses existing values in the DB to list dates. I think this will work for me</p>
  
  <pre><code>SELECT
      a.date_time,
      b.temperature as FishTank,
      c.temperature as Room
  FROM (
      select
          convert((min(date_time) div 100)*100, datetime)  as date_time
      from
          raspicontroller.temperature
      group by 
          date_time div 100
  ) as a
  LEFT JOIN (
      select
          serial,
          convert((min(date_time) div 100)*100, datetime)  as date_time, 
          avg(temperature) as temperature
      from
          raspicontroller.temperature
      WHERE
          serial like '28-000898430d59'
      group by 
          date_time div 100
  ) as b on a.date_time = b.date_time
  LEFT JOIN (
      select
          serial,
          convert((min(date_time) div 100)*100, datetime)  as date_time, 
          avg(temperature) as temperature
      from
          raspicontroller.temperature
      WHERE
          serial like '28-000f9843201e'
      group by 
          date_time div 100
  ) as c on a.date_time = c.date_time
  </code></pre>