StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -</p> <ol> <li><p>Create a table that only holds incrementing numbers - easy to do using an auto_increment:</p> <pre><code>DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; </code></pre></li> <li><p>Populate the table using:</p> <pre><code>INSERT INTO NUMBERS (id) VALUES (NULL) </code></pre> <p>...for as many values as you need.</p></li> <li><p>Use <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add" rel="nofollow noreferrer">DATE_ADD</a> to construct a list of dates, increasing the days based on the <code>NUMBERS.id</code> value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, "YYYY-MM-DD HH:MM:SS") -</p> <pre><code>SELECT x.* FROM (SELECT DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE) FROM numbers n WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 MINUTE) <= '2010-06-14' ) x </code></pre></li> <li><p>LEFT JOIN onto your table of data based on the time portion:</p> <pre><code> SELECT x.ts AS timestamp, COALESCE(yt.col, 'missing') AS cnt FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE), '%Y-%m-%d %k:%i') AS ts FROM numbers n WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE) <= '2010-06-14') x LEFT JOIN YOUR_TABLE yt ON DATE_FORMAT(yt.date, '%Y-%m-%d %k:%i') = x.ts </code></pre></li> </ol> <p>See the documentation for more info on the <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format" rel="nofollow noreferrer">DATE_FORMAT function</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload