Most commented posts by time period (last 12h, last 24h and etc)

So if you want to get the posts that were commented within the last xx hours, e.g. 12 hours, then try one of the following:

Note though, I’m selecting only published posts (post_status="publish") and of the post type (post_type="post").

And just replace the value of $hours with your preferred number of hours.

• Using TIMESTAMPDIFF().

  $hours = 12;
  
  $result = $wpdb->get_results( $wpdb->prepare( "
      SELECT p.comment_count, p.ID, p.post_title
          FROM $wpdb->posts p
          INNER JOIN $wpdb->comments c ON c.comment_post_ID = p.ID
      WHERE p.post_type="post"
          AND p.post_status="publish"
          AND ( TIMESTAMPDIFF( HOUR, c.comment_date, NOW() ) <= %d )
      GROUP BY p.ID
      ORDER BY p.comment_count DESC
      LIMIT 0, 25
  ", $hours ) );
  

• Or calculate the date and time of xx hours ago and use comment_date >= <date-time xx hours ago>.

  $hours = 12;
  
  $timestamp = current_time( 'timestamp' );
  $date_ago = date( 'Y-m-d H:i:s', $timestamp - $hours * HOUR_IN_SECONDS );
  
  $result = $wpdb->get_results( $wpdb->prepare( "
      SELECT p.comment_count, p.ID, p.post_title
          FROM $wpdb->posts p
          INNER JOIN $wpdb->comments c ON c.comment_post_ID = p.ID
      WHERE p.post_type="post"
          AND p.post_status="publish"
          AND c.comment_date >= %s
      GROUP BY p.ID
      ORDER BY p.comment_count DESC
      LIMIT 0, 25
  ", $date_ago ) );