How to get the average post-time (hour) of day in WordPress?

1 Views

I was trying to build a page(template) to display my “Writing behavior” of my blog. To show which hour I post most in a day of all time, I need to calculate the “average post-time (hour) of day”, so that I could know like”uh I usually post blog in the morning…”

The first idea came to my mind is sum all “time(hours)” together, then divide them by “total post count”, then translate to 24hour format to show up, but seems my code didn’t work.

Read More

The core code I wrote is like this: 

<?php
$alltimes=$wpdb->get_var($wpdb->prepare("SELECT sum(post_date) FROM $wpdb->posts WHERE post_type = 'post'", $alltimes));
echo '<p>Total post count '.$published_posts.'</p>';
$count_posts = wp_count_posts(); $published_posts = $count_posts->publish;
$averagetime= $alltimes / $published_posts;
echo '<p>Average Time count is '.$averagetime.'</p>';
echo'<p>Average time is '.date('Y-m-d h:i:s',$averagetime).'</p>';
?>

My mistake is the year,month,day,hour,sec.. all summed up to “$alltimes”, so when I divide it will become a disaster.

Maybe if I could get the “hour” in “post_date” but not the whole “post_date” will do the job, but how to?

Thank you for your time.

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. I think you’re looking for a query like this. I’m using HOUR() and DATE() to extract the date and hour parts from the datetime value:

    SELECT HOUR(post_date) hour, COUNT(*) posts FROM wp_posts 
WHERE post_type = 'post' && DATE(post_date) = DATE(NOW()) 
GROUP BY hour
ORDER BY posts DESC;

    This will show how many posts have been made during different hours of the day, notice that this only will return rows where post_date match the current date. If you would like to get the most hour based on all posts you just need to remove the DATE(post_date) = DATE(NOW()) condition:

    +------+-------+
| hour | posts |
+------+-------+
|   18 |    15 |
|   19 |    12 |
|   21 |     2 |
+------+-------+

    If you only would like to get one single row that contains the hour with the maximum number of posts, this would work:

    SELECT hour, MAX(posts) posts FROM ( 
  SELECT HOUR(post_date) hour, COUNT(*) posts FROM wp_posts 
  WHERE post_type ='post' && DATE(post_date) = DATE(NOW()) 
  GROUP BY hour ORDER BY posts DESC 
) v;

    or like this:

    SELECT HOUR(post_date) hour, COUNT(*) posts FROM wp_posts 
WHERE post_type = 'post' && DATE(post_date) = DATE(NOW()) 
GROUP BY hour
ORDER BY posts DESC
LIMIT 1

    Reference
    Mysql – Date and Time Functions