How to get posts and comments amount per hour, per year and per month?

Question

I have this snippet:

function count_user_comments_today( $uid ){
    global $wpdb;
    $today = date('Y-m-d');
    $tomorrow = date('Y-m-d', time() + 86400);
    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->comments} WHERE user_id = %d  AND comment_date >= %s  AND comment_date < %s ", $uid, $today, $tomorrow  ));
    return $count;
}

But this get's the comments per day and that for a specific user. I want it to be generic, for the whole site and per hour, per year and per month.... Is this possible? Of course also the posts and not only the comments.

Answer 1

For comments:

function count_comments_in_period( $date_from, $date_to ){
    global $wpdb;
    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_date >= %s  AND comment_date < %s ", $date_from, $date_to));
    return $count;
}

Just use dates in yyyy-mm-dd format.

For posts:

function count_posts_in_period( $date_from, $date_to ){
    global $wpdb;
    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_date >= %s  AND post_date < %s ", $date_from, $date_to));
    return $count;
}

Answer 2

I don't have time to build both functions and I don't have time to test this one very much but I think this is at least close:

function funky_comment_query_wpse_108830($year, $month, $hour) {

  if (!empty($year)) {
    $sql[] = "YEAR(comment_date) = {$year}";
  }
  if (!empty($month)) {
    $sql[] = "MONTH(comment_date) = {$month}";
  }
  if (!empty($hour)) {
    $sql[] = "HOUR(comment_date) = {$hour}";
  }
  if (empty($sql)) {
    return;
  }

  global $wpdb;
  $sql = implode(' AND ',$sql);

  $count = $wpdb->get_var("SELECT count(comment_ID) FROM {$wpdb->comments} WHERE {$sql}");
  return $count;
}
echo funky_comment_query_wpse_108830('2013','','');