Postgres Time Series Count() issue

Grafana Time Series Panel
1

  • What Grafana version and what operating system are you using?

  • grafana-oss docker-compose

  • What are you trying to achieve?

  • I have postgres as my storage, trying to create a simple time series graph. My two columns are “time” and “events”. I am trying to count each even that happened per timestamp. This is how my timestamp appears in postgres “2025-04-09T13:06:33.453”, and my events can be for example “open” “closed”.

  • How are you trying to achieve it?

  • This is the current sql query I have attempted

SELECT 
  DATE_TRUNC('second', "time") time, 
  COUNT(event) AS "open" 
FROM myTable 
WHERE event='open' 
GROUP BY type, "time"

I am not an expert in SQL queries just a note up front.

  • What happened?

  • It seems to count them but it for some reason will have more that one same timestamp. So instead of for example all being 2025-04-09T13:06:33 - 10 , it will be 2025-04-09T13:06:33 - 2 , 2025-04-09T13:06:33 - 3 , 2025-04-09T13:06:33 - 1 , 2025-04-09T13:06:33 - 1 , 2025-04-09T13:06:33 - 3

  • What did you expect to happen?
    My goal is to combine all my events to the second to display it easier for the user. For this example, there is a lot of events per millisecond so its hard to read.

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?

2

Group only by time:

GROUP BY "time"
1 Like
3

Use this when you just want the total number of “open” events per second, regardless of any other column.

SELECT
  DATE_TRUNC('second', "time") AS time,
  COUNT(*) AS open_count
FROM myTable
WHERE event = 'open'
GROUP BY DATE_TRUNC('second', "time")
ORDER BY time;