StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>No stored procedures, temporary tables, only one query, and an efficient execution plan given an index on the date column:</p> <pre><code>select subdate( '2012-12-31', floor(dateDiff('2012-12-31', dateStampColumn) / 30) * 30 + 30 - 1 ) as "period starting", subdate( '2012-12-31', floor(dateDiff('2012-12-31', dateStampColumn) / 30) * 30 ) as "period ending", count(*) from YOURTABLE group by floor(dateDiff('2012-12-31', dateStampColumn) / 30); </code></pre> <p>It should be pretty obvious what is happening here, except for this incantation:</p> <pre><code>floor(dateDiff('2012-12-31', dateStampColumn) / 30) </code></pre> <p>That expression appears several times, and it evaluates to the number of 30-day periods ago <code>dateStampColumn</code> is. <code>dateDiff</code> returns the difference in days, divide it by 30 to get it in 30-day periods, and feed it all to <code>floor()</code> to round it to an integer. Once we have this number, we can <code>GROUP BY</code> it, and further we do a bit of math to translate this number back into the starting and ending dates of the period.</p> <p>Replace <code>'2012-12-31'</code> with <code>now()</code> if you prefer. Here's some sample data:</p> <pre><code>CREATE TABLE YOURTABLE (`Id` int, `dateStampColumn` datetime); INSERT INTO YOURTABLE (`Id`, `dateStampColumn`) VALUES (1, '2012-10-15 02:00:00'), (1, '2012-10-17 02:00:00'), (1, '2012-10-30 02:00:00'), (1, '2012-10-31 02:00:00'), (1, '2012-11-01 02:00:00'), (1, '2012-11-02 02:00:00'), (1, '2012-11-18 02:00:00'), (1, '2012-11-19 02:00:00'), (1, '2012-11-21 02:00:00'), (1, '2012-11-25 02:00:00'), (1, '2012-11-25 02:00:00'), (1, '2012-11-26 02:00:00'), (1, '2012-11-26 02:00:00'), (1, '2012-11-24 02:00:00'), (1, '2012-11-23 02:00:00'), (1, '2012-11-28 02:00:00'), (1, '2012-11-29 02:00:00'), (1, '2012-11-30 02:00:00'), (1, '2012-12-01 02:00:00'), (1, '2012-12-02 02:00:00'), (1, '2012-12-15 02:00:00'), (1, '2012-12-17 02:00:00'), (1, '2012-12-18 02:00:00'), (1, '2012-12-19 02:00:00'), (1, '2012-12-21 02:00:00'), (1, '2012-12-25 02:00:00'), (1, '2012-12-25 02:00:00'), (1, '2012-12-26 02:00:00'), (1, '2012-12-26 02:00:00'), (1, '2012-12-24 02:00:00'), (1, '2012-12-23 02:00:00'), (1, '2012-12-31 02:00:00'), (1, '2012-12-30 02:00:00'), (1, '2012-12-28 02:00:00'), (1, '2012-12-28 02:00:00'), (1, '2012-12-30 02:00:00'); </code></pre> <p>And the result:</p> <pre><code>period starting period ending count(*) 2012-12-02 2012-12-31 17 2012-11-02 2012-12-01 14 2012-10-03 2012-11-01 5 </code></pre> <p>period endpoints are inclusive.</p> <p>Play with this in <a href="http://www.sqlfiddle.com/#!2/6d778/9" rel="nofollow noreferrer">SQL Fiddle</a>.</p> <p>There's a bit of potential goofiness in that any 30 day period with zero matching rows will not be included in the result. If you could join this against a table of periods, that could be eliminated. However, MySQL doesn't have anything like PostgreSQL's <a href="http://www.postgresql.org/docs/8.2/static/functions-srf.html" rel="nofollow noreferrer">generate_series()</a>, so you'd have to deal with it in your application or try <a href="https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql">this clever hack</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