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:

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:

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.