There is an earlier question on StackOverflow relating to this subject (can place the hyperlink, I have got no rights for now) titled "MySQL date comparison filter", which goes being an extension of that certain.
I have got a bit of authors on my small WordPress blog and I must obtain productivity through MySQL. The following query works pretty much under MySQL to obtain an author's publish throughout certain time range just one day:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE wp_posts.post_type = 'post' AND post_author = '50' AND post_date BETWEEN STR_TO_DATE('2011-10-27 14:19:17','%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2011-10-27 14:51:17','%Y-%m-%d %H:%i:%s') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 100
However it provides me with only the posts of this day throughout that hour range. Let me obtain a table with with everyday data chock-full for every day and every author. On every day and every author, there must be the amount of posts released with that author tomorrow as well as on that hour range.
The output ought to be something similar to this:
October Auth1 Auth2 Auth3 1 0 0 0 2 0 0 0 3 0 1 0 4 0 2 0 5 1 0 0 6 0 2 0 7 0 0 0 8 3 0 0 9 0 0 0 10 5 1 0 11 1 0 0 ... 31 2 1 1
Therefore the date ought to be a flexible, but Let me include all authors, so I'd take away the publish_author AND line.
I am no expert at MySQL however i question if this may be done pretty much easily and export the query results (or, more exactly, some fields from the query results) like a table, such as the one proven.
I am not likely to rewrite your whole query, but here's how you'd perform the data grouping:
SELECT ... FROM ... WHERE YEAR(post_date) = 2011 AND MONTH(post_date) = 10 GROUP BY DAY(post_date), HOUR(post_date)
Creating multiple posts for every author isn't a good utilization of a question. That kind of transformation is much better completed in your wordpress code.
Observe that this question works on exact clock periods, 1am, 2am, 3am, etc... If you want arbitrary occasions (1:05am, 2:05am, 3:05am, etc...), this will not work and you will need a more difficult grouping.