Grafana graphing with Postgres JSON datatype not working

Question

I'm setting up Grafana for a internal dashboard for the company I work at. We have a phone system that we track, and have metrics saved in a database. I've extracted key metrics and stored them on a Postgres database. Here is what it looks like:

postgres=# SELECT * FROM phonedata LIMIT 5;
 id |    date    |                                       data                                       
----+------------+----------------------------------------------------------------------------------
 16 | 2019-05-27 | {"avgDuration": "608", "avgQueuetime": "0:02:46.716667", "voicemailCount": "6"}
 17 | 2019-05-26 | {"avgDuration": "676", "avgQueuetime": "None", "voicemailCount": "0"}
 18 | 2019-05-25 | {"avgDuration": "506", "avgQueuetime": "0:01:32.684211", "voicemailCount": "1"}
 19 | 2019-05-24 | {"avgDuration": "540", "avgQueuetime": "0:02:14.784091", "voicemailCount": "11"}
 20 | 2019-05-23 | {"avgDuration": "616", "avgQueuetime": "0:03:09.433962", "voicemailCount": "10"}
(5 rows)

This data shows the statistics for the date as shown in the "date" column. This all works fine. I have data for the last 90 days so there is no problem with lack of data.

Now I'm trying to make a graph on Grafana that shows the average duration of a phone call per day, bit I can't get it working.

Here is a link to a image of the code I have written and the graph on Grafana. StackOverflow won't let me post the image directly

https://i.imgur.com/3uQe9t5.png

Here is the code written out:

SELECT
    date AS "time",
    data->>'avgDuration'::VARCHAR as "values"
FROM
    phonedata
WHERE
    $__timeFilter(date) AND
    data->>'avgDuration' NOT ilike 'None'
ORDER BY 1

I feel like I'm very close but not quite there yet. I have tried many different variations on this but can't figure it out.

Any help would be tremendous.

Thanks

(I'm running Grafana v5.4.2 (commit: d812109))

LordStabbyNZ LordStabbyNZ · Accepted Answer · 2019-06-04T03:13:38

I have got it working.

I believe it was caused by timezones. I found a discussion on some Grafana forums discussing a issue where when returning a data all it would display on a table was "1.56 Tri". I changed the schema on the database, I changed the data column from type DATE to type TIMESTAMPTZ.

This is what the PostgreSQL table looks like now:

postgres=# SELECT * FROM phonedata LIMIT 5;
 id |          date          |                                   data
----+------------------------+---------------------------------------------------------------------------
  1 | 2019-06-03 00:00:00+12 | {"avgDuration": "543", "avgQueuetime":"0:04:13", "voicemailCount": "7"}
  2 | 2019-06-02 00:00:00+12 | {"avgDuration": "524", "avgQueuetime":"None", "voicemailCount": "2"}
  3 | 2019-06-01 00:00:00+12 | {"avgDuration": "573", "avgQueuetime":"0:03:04", "voicemailCount": "6"}
  4 | 2019-05-31 00:00:00+12 | {"avgDuration": "621", "avgQueuetime":"0:02:14", "voicemailCount": "15"}
  5 | 2019-05-30 00:00:00+12 | {"avgDuration": "729", "avgQueuetime":"0:02:39", "voicemailCount": "5"}
(5 rows)

And this is what the Grafana code looks like now:

SELECT
  phonedata.date AS time,
  ((data->>'avgDuration')::NUMERIC) as values
FROM
  phonedata
WHERE
  $__timeFilter(phonedata.date)

This is what the graph looks like now:

I also discovered if any of the values can't be cast to NUMERIC the graph wont continue graphing. I would have a day where the avgDuration was 'None' as no phone calls were made that day, and it would disrupt the graph. So if it happened 5 days ago, it would graph the first 4 days, fail on day 5, and not go to day 6 onwards.

Grafana graphing with Postgres JSON datatype not working

1 Answers