I have a Postgresql DataSource with the following table:

It's kinda logs. All I want is to show on a chart how many successful records (with http_status == 200) do I have per each hour. Sounds simple, right? I wrote this query:

SELECT
  count(http_status) AS "suuccess_total_count_per_hour",
  date_trunc('hour', created_at) "log_date"
FROM logs
WHERE
  http_status = 200
GROUP BY log_date
ORDER BY log_date

It gives me the following result:

Looks good to me. I'm going ahead and trying to put it into Grafana:

Ok, I get it, I have to help Grafana to understand where is the field for time count. I go to Query Builder and I see that it breaks me query at all. And since that moment I got lost completely. Here is the Query Builder screen:

How to explain to Grafana what do I want? I want just a simple chart like:

Sorry for the rough picture, but I think you got the idea. Thanks for any help.

CodePudding user response：

1. Your time column (e.g. created_at) should be TIMESTAMP WITH TIME ZONE type*
2. Use time condition, Grafana has macro so it will be easy, e.g. WHERE $__timeFilter(created_at)
3. You want to have hourly grouping, so you need to write select for that. Again Grafana has macro: $__timeGroupAlias(created_at,1h,0)

So final Grafana SQL query (not tested, so it may need some minor tweaks):

SELECT 
  $__timeGroupAlias(created_at,1h,0),
  count(*) AS value,
  'succcess_total_count_per_hour' as metric
FROM logs
WHERE
  $__timeFilter(created_at)
  AND http_status = 200
GROUP BY 1
ORDER BY 1

*See Grafana doc: https://grafana.com/docs/grafana/latest/datasources/postgres/ There are documented macros. There are also macros for the case, when your time column is UNIX timestamp.