mysql - query to fill in missing datetime values I have a Table similar to:

CREATE TABLE `stats` (
    `stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stat_Start` timestamp NULL DEFAULT NULL,
    `stat_values` smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (`stat_ID`),
    );


It contains a list of events with the time it happenned, and the value. 

I am now looking to generate a report from this similar to:

SELECT
    LEFT(stat_start,16) AS `time`,
    AVG(`stat_values`) AS `data` 
FROM stats s 
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16) 
ORDER BY stat_start;


In other words, get the Average value for all entries per minute for the past hour.

However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute. 
Is there a way of achieving this in MySQL?

so I might get something like:

'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --&gt; row for which no events were recorded
'2017-04-07 09:03', 132
Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:

Create have a table all_minutes with all possible minutes (in some finite range).  (If using MariaDB, use a "Sequence" table.)  Then

SELECT a.time,
       IFNULL(s.data, 0)  AS `data`  -- assuming you want 0, not NULL
    FROM all_minutes AS a
    LEFT JOIN ( SELECT
            LEFT(stat_start,16) AS `time`,  -- eg, '2017-04-07 11:03'
            AVG(`stat_values`) AS `data` 
        FROM stats
        WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
        GROUP BY `time`
        ORDER BY `time`    -- note a change here
              ) AS s    ON s.time = a.time
    WHERE a.time BETWEEN ... AND ...; -- whatever range you need


Note:  Both tables should exclude or include the :00 for seconds.  Otherwise (I think) there could be some difficulty in doing the = in the ON.