[SOLVED] Simple Graph from Postgres data source is not working

Grafana Time Series Panel
1

Hi,

i’m trying to build a graph panel to monitor the ammount of support tickets per day.

Data source is a postgresql database. Within the table there are the follwoing columns:

  • “created” - timestamp without timezone //ticket creation time
  • “ID” - integer //ticket id
  • some other, not relevant columns

Datasource is set up and working; it´s used in other panels succesfully.

My query is build like this:

select
  EXTRACT(EPOCH from "created") as time
  ,count("ID") as value
  ,'Count' as metric
from
  tickets
group by
  "created"

Metrics are formatted as timeseries, all other options are default.

Grafana shows the message “data points outside time range” and nothing is shown as graph. If I switch X-Axis to mode “Series” there is one single bar shown as graph, if I switch to mode “histogram” there are shown 3 bars: on at x-coord1.0 with count = 3538, one at x-coord 2.0 with count 159 and one at 3.0 with count 7

In any Query-Tool or in a Table-Panel in Grafana my query shows that there are between 55 and 202 tickets by day.

Can anyone leave a hint, what´s wrong in my config or query?

Thx,

Michael

2

I modified my data and query.
By now i´m filling a column just with a 1 so i can use SUM() in my query.

New query looks like this:

select
“created”::date as time
,sum(“value”) as value
from
tickets
group by
“created”::date
order by
“created”::date

If then i turn X-Axis mode to series and value to current it shows the metrics for the last day present in data. But not for all the days before.

Is there a way to display these values?

3

I think you have a timezone issue. Please make sure to store timestamps as UTC timezone or store in a column with a datatype that can include timezone.

4

Thx for replying.

I solved my issue with some try & error cycles and finished with a query like this:

SELECT
	CAST("created"::date AS VARCHAR(10)) AS metric
	, SUM("count") AS value
	, EXTRACT(EPOCH FROM date_trunc('day',"created")) AS time
FROM
	tickets
WHERE
	date_trunc('day',"created") >= current_date - interval '30 days'							
GROUP BY
	CAST("created"::date AS VARCHAR(10)), date_trunc('day',"created")
ORDER BY
	3

Based on this i was able to get some other graphs too.