Help Getting Started With Postgresql

Grafana
1

I’m a beginner to Grafana and databasse and I’m trying to use grafana to chart some data from a Postgresql database.

I’ve set up the datasource OK, but I’m having trouble creating a chart. The data in the database looks like the following (I’ve left off the rest of the columns). The created_at column is a string and I’ve tried to convert it to a datetime in Grafana but I’m really struggling. I basically want to count the number of rows created each day and chart this on a histogram.

|id|created_at|
|1|2017-06-15 07:12:07 UTC|
|2|2017-06-15 07:12:08 UTC|
|3|2017-06-15 07:12:08 UTC|

SELECT
TO_TIMESTAMP(created_at,‘YYYY-MM-DD HH24:MI:SS’) AS time,
count(created_at) As entry_count
FROM
assets_survey_form_v1_0
GROUP by
time

The above query works but groups the entries by the second. I’ve tried using timegroups and have been through the docs. But can’t get this to work. Any idea where I’m going wrong and how I can get this to work?

2

The following query should group values by hour, but you should probably also fix your schema or make view around your table to make working with it easier

SELECT
date_trunc('hour',to_timestamp(created_at,‘YYYY-MM-DD HH24:MI:SS’)) AS time,
count(created_at) As entry_count
FROM
assets_survey_form_v1_0
GROUP by 1
3

Thanks a lot. That’s gotten me moving and I’ve managed to get the chart working.

I’m now trying to group the metrics but I keep getting an error
"column metric must be of type char, varchar or text, got:

I’m using the following SQL query

SELECT
date_trunc('day',to_timestamp(concat_ws(' ', survey_date, survey_time),'DD/MM/YYYY HH24:MI')) AS time,
count(survey_date) As entry_count,
cast(dma as text) As dma_name
FROM
assets_survey_form_v1_0
GROUP by
time, dma_name
ORDER by
time

For info, the following works:

SELECT
date_trunc('day',to_timestamp(concat_ws(' ', survey_date, survey_time),'DD/MM/YYYY HH24:MI')) AS time,
count(survey_date) As entry_count
FROM
assets_survey_form_v1_0
GROUP by
time
ORDER by
time
4

The following should work:

SELECT
date_trunc('day',to_timestamp(concat_ws(' ', survey_date, survey_time),'DD/MM/YYYY HH24:MI')) AS time,
count(survey_date) As entry_count,
cast(dma as text) As metric
FROM
assets_survey_form_v1_0
GROUP by 1, 3
ORDER by
time

The column with the name for the series needs to be named metric. Even though in latest grafana that restriction got removed and any text column will be treated as series name

1 Like
5

Thanks, that worked a treat.