Wordpress posts per hour - Wordpress Solution - Coding

I'm looking to create a simple graph thats not too crazy; its a bar graph; base on the number of wordpress posts in each hour. So I have a lot of posts and I want to see how many each hour in a bar graph. Data would like like this for example; it may be on avg from 80 to 400 posts depends on the days. what wold be an efficient way to go about this? Update: just to update based on Arnav's question; I just looking for the query of the db in Wordpress; I can manipulate the data after. Timespan would be one day; 00:00 : 2 01:00 : 1 02:00 : 0 03:00 : 0 04:00 : 3 05:00 : 1 06:00 : 4 07:00 : 12 08:00 : 15 09:00 : 24 10:00 : 22 11:00 : 25 12:00 : 32 13:00 : 45 14:00 : 55 15:00 : 62 16:00 : 67 17:00 : 54 18:00 : 32 19:00 : 31 20:00 : 17 21:00 : 8 22:00 : 4 23:00 : 2

Solution - 1

have you added code for the bar graph?

Solution - 2

Do you know how to code? If you know how to code that could be solved with a scheduled CRON event, every second check the new posts https://developer.wordpress.org/plugins/cron/scheduling-wp-cron-events/

Solution - 3

this is not very efficient but it should do the job and it's easy to implement. Then you could run it once a day and store the $stats array somewhere so that then you don't have to do all the queries again every time you load the graph.


        $stats = [];
        $today = getdate();

        // repeat this for each hour
        $args = [
            'posts_per_page' => -1,
            'fields' => 'ids',
            'date_query' => [
                [
                    'year'  => $today['year'],
                    'month' => $today['mon'],
                    'day'   => $today['mday'],
                ],
                [
                    'hour'      => 0,
                    'compare'   => '>=',
                ],
                [
                    'hour'      => 1,
                    'compare'   => '<',
                ],
            ]
        ];
        $query = new WP_Query($args);

        $stats['00:00'] = $query->found_posts;

the idea is to make one query for each hour, and then count the posts and add them to an array which then you can use to make the graph. p.s. in the example code I just did the query for the first hour to show how to do it. Then you'd have to repeat that query 12 times tweaking the hours range so that you count and store posts for all 12 hours)

Solution - 4

using mysql query approach as following:

SELECT 
DATE(post_date) AS Day, 
HOUR(TIME(post_date)) AS Hour, 
COUNT(*) as Count
FROM prefix_posts
WHERE post_date LIKE '2020-01-02%' 
GROUP BY Day, Hour

Solution - 5

I like Andrea idea, but I added some codes and wrapped they into a function that close to your need:


function posts_per_hour($y,$m,$d){

	$posts_per_hour = [];

	for( $i = 0; $i < 24; $i++ ){
 	$args = array(
		    'date_query' => array(
		        array(
		            'year'  => $y,
		            'month' => $m,
		            'day'   => $d,
		        ),		    	
		        array(
		            'hour'      => $i,
		            'compare'   => '=',
		        ),
		        array(
		            'minute '   => 0,
		            'compare'   => '>=',
		        ),
		        array(
		            'minute '   => 59,
		            'compare'   => '<=',
		        ),
		    ),
		    'post_type' => 'post',
		    'posts_per_page' => -1,
		);
		$query = new WP_Query( $args );				
		$posts_per_hour[] = $query->found_posts;
	}
	return  $posts_per_hour;
}

$posts_per_hour = posts_per_hour(2020,2,26);    // call the function with your parameters: year, month, day

echo $posts_per_hour[0];    // number of posts in the hour 00:00
echo $posts_per_hour[1];    // number of posts in the hour 01:00
.
.
.
echo $posts_per_hour[23];    // number of posts in the hour 23:00

From here you can get number of posts per hour for each day in specific month and year

Solution - 6

Hi, Please try the following query, It will help you


select date_format(post_date, '%h:00') postTime, count(*) as postCount from wp_posts_table group by postTime order by postTime

This query will return all posts with posts count in their time slot, If you need to get any specific date's data, please follow the below code


select date_format(post_date, '%h:00') postTime, count(*) as postCount from wp_posts_table where DATE(post_date) = DATE('2016-01-14') group by postTime order by postTime

If you need to get data between 2 dates, please follow the below code


select date_format(post_date, '%h:00') postTime, count(*) as postCount from wp_posts_table where DATE(post_date) >= DATE('2016-01-14') and DATE(post_date) <= DATE('2016-02-01') group by postTime order by postTime

You can replace the table name wp_posts_table with your table name.

Solution - 7

Hello, This code will do the trick


for($x=1;$x<=24;$x++){	
    $hours = $x-1;
    $y= $x+1;
    	$allPosts= get_posts(array(
            'numberposts' => -1,
            'post_status' => 'publish',
            'orderby' => 'date',
            'order'   => 'DESC',
            'date_query'    => array(
 'column'  => 'post_date',
                'after'     => -$x.' hour',
            'before'    => $x .' hour',

            )
        ));
            $allPostsNum = count($allPosts); 
            
           
	$posts= get_posts(array(
            'numberposts' => -1,
            'post_status' => 'publish',
            'orderby' => 'date',
            'order'   => 'DESC',
            'date_query'    => array(
                'column'  => 'post_date',
                'after'   => -$hours.' hours', // -7 Means last 7 days 
                'before'    => $hours .' hour',

            )
        ));
        
   
        $hourPosts = count($posts);
        $postsNum = $allPostsNum-$hourPosts;
echo $x.':00 : '. $postsNum.'<br/>';
}

Solution - 8

have you tried using a plugin?

Wordpress

Related
Website recently received a
The little little question... where is the url of the taxo... ? - Wordpress Solution
Events calendar description dont display on mobile divices - Wordpress Solution
How to force/trigger the load of Wordpress native lazy loading images without scrolling (with a js to run 3 seconds after page load)? - Wordpress Solution
Need help with a PHP IF statement - Wordpress Solution

Type:	Code Example
Category:	Coding
Sub Category:	Code Example
Uploaded by:	Admin
Views:	25