# How to have grafana show number of entries per hour

**URL:** <https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411>\
**Category:** MySQL\
**Tags:** mysql\
**Created:** [February 12, 2018, 5:55pm UTC](https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411 "2018-02-12T17:55:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![wered](https://avatars.discourse-cdn.com/v4/letter/w/f19dbf/32.png) [@wered](https://community.grafana.com/u/wered)\
**Post date:** [February 12, 2018, 5:55pm UTC](https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411/1 "2018-02-12T17:55:07Z")

</div>

Im using a MySQL data source with a table that records searches from a form, I would like to have grafana to show a line graph that plots the number of searches that were done each hour. Would anyone be able to provide an example of what the query would look like to get grafana to do that? My database has 4 fields, an auto-incrementing id, text data of what was searched for, timestamp of the search, and which site the search was from.

---

<div class="post-metadata">

**Author:** ![davewaters](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/davewaters/32/7308_2.png) [@davewaters](https://community.grafana.com/u/davewaters)\
**Post date:** [February 12, 2018, 7:07pm UTC](https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411/2 "2018-02-12T19:07:12Z")

</div>

The Single State makes this easy. Assuming your data source is one search per entry (and your question seemed to indicate as much):

- Add the Single Stat Plugin
- Edit it’s settings
- Select the Metrics tab
- Select your Data Source
- For your Query (which can be blank) change the Metric to ‘Count’
- Group by: Date Histogram, Field=timestamp (your timestamp field)
- Interval - **this is key, set it to 1h (for events per hour)**

Then, select the Options tab  
Under Value,Stat, select ‘Average’

When your plugin is done refreshing, you should see events per hour.

Under Options-Value-Prefix/Postfix you can add text to dress up the value as well.

We use this to track an events per second metric, and I’m also using Elasticsearch for the Data Source, but the plug-in settings should be similar.

---

<div class="post-metadata">

**Author:** ![wered](https://avatars.discourse-cdn.com/v4/letter/w/f19dbf/32.png) [@wered](https://community.grafana.com/u/wered)\
**Post date:** [February 12, 2018, 10:15pm UTC](https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411/3 "2018-02-12T22:15:04Z")

</div>

Thanks, The settings for MySQL are different and it did not work however I copied the data into elastic search and used that and it worked like a charm.

---

<div class="post-metadata">

**Author:** ![anon34853223](https://avatars.discourse-cdn.com/v4/letter/a/71c47a/32.png) [@anon34853223](https://community.grafana.com/u/anon34853223)\
**Post date:** [February 13, 2018, 11:38am UTC](https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411/4 "2018-02-13T11:38:51Z")

</div>

Hi,

Using mysql something like this should work to plot searches per site and hour on a line graph:

```sql
SELECT
  $__timeGroup(<your timestamp field>,'1h') as time_sec,
  COUNT(<your id field>) as value,
  <your site name field> as metric
FROM test_data
WHERE $__timeFilter(<your timestamp field>)
GROUP BY 1, <your site name field>
ORDER BY 1  

```

Please note that I’m using the beta release of Grafana 5.0 and the $\_\_timeGroup macro function is _not_ available in the latest stable v4.6.3.

See [Using MySQL in Grafana documentation](http://docs.grafana.org/features/datasources/mysql/#using-mysql-in-grafana) for further information.

Marcus

---

<div class="post-metadata">

**Author:** ![danilogimenes](https://avatars.discourse-cdn.com/v4/letter/d/65b543/32.png) [@danilogimenes](https://community.grafana.com/u/danilogimenes)\
**Post date:** [June 22, 2021, 1:15am UTC](https://community.grafana.com/t/how-to-have-grafana-show-number-of-entries-per-hour/5411/5 "2021-06-22T01:15:17Z")

</div>

Hi,

thx, this saved me