# Hour By Hour Statistics

**URL:** <https://meta.discourse.org/t/hour-by-hour-statistics/170223>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [November 15, 2020, 10:53pm UTC](https://meta.discourse.org/t/hour-by-hour-statistics/170223 "2020-11-15T22:53:13Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![RGJ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/rgj/32/523185_2.png) [@RGJ](https://meta.discourse.org/u/RGJ)\
**Post date:** [November 16, 2020, 5:12pm UTC](https://meta.discourse.org/t/hour-by-hour-statistics/170223/6 "2020-11-16T17:12:22Z")

</div>

The query below will show posts created per hour of the day.

```
-- [params]
-- int :months_ago = 1

WITH query_period AS
(SELECT date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' AS period_start,
                                                    date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' AS period_end)
SELECT EXTRACT (HOUR FROM p.created_at) AS hour,
  COUNT(p.id) AS amount
FROM posts p
JOIN topics t ON t.id = p.topic_id
JOIN query_period qp ON p.created_at >= qp.period_start
AND p.created_at <= qp.period_end
WHERE t.archetype = 'regular'
AND p.user_id > 0
GROUP BY hour
ORDER BY hour

```

_The [data explorer](https://meta.discourse.org/t/32566?silent=true) plugin is one of the plugins that we install by default. All you need to do is enable it in Admin - Plugins. If you have any questions specific to our hosting please take them to [support@discoursehosting.com](mailto:support@discoursehosting.com)._

---

_[View the full topic](https://meta.discourse.org/t/hour-by-hour-statistics/170223)._