PostgreSQL select statement works in pgAdmin4 but not in Grafana "converting time columns failed"

Grafana PostgreSQL
1

Hi,
New to the forum and PostgreSQL.
I have used Grafana in the past with SQLite and the same data, which worked correctly in V9.3.2 and created a chart with time & reading to display a graph.

Now I am using PostgreSQL, but cannot get it to work, looked all over internet but can’t get it to work, I am a newbie at most of this, so any help greatly appreciated, thanks … Gaz

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

  • What are you trying to achieve?
    A time series chart of a ISO timestamp & data readings

  • How are you trying to achieve it?
    SELECT
    device_data → ‘meter_reading’ AS meter_reading,
    device_data → ‘iso’ AS time
    FROM energy
    WHERE device = ‘IP22’

  • What happened?
    Status: 500. Message: converting time columns failed: failed to convert time column: column type “*string” is not convertible to time.Time


The data in my PostgreSQL database
{
  "cost": 0.6,
  "iso": "2023-12-05T09:12:38.046Z",
  "location": "Garage - Electicity",
  "meter_reading": 1.358,
  "timestamp": 1701767558,
  "total_cost": 0
}

An image from pgAdmin4 …
30

2

Grafana needs timeseries = that time dimension is important. It looks like your iso column is a string type, which contains datetime. But you need timestamp type, so try to use TO_TIMESTAMP to convert string type to timestamp type or you have already timestamp column which looks like a timestamp type, so you can use it.
Be familiar with PostgreSQL data types: timestamp vs string.

1 Like
3

Hi

I changed a few things as you mentioned now using timestamp from database and in grafana the AS time has to be changed to AS timestamp then I get data.

BUT I cannot get a graph from it only a Table ?
When I try the graph option I get Data is missing a number field

At least it’s good progress than you for your help, any suggestion what I am now doing wrong ?

A image of Grafana GUI now …

31
31883×786 43.8 KB

4

See your screenshot. You format result as a table, format it as timeseries, then you should be able to use timeseries panel type.

5

Hi,

Yes i tried that but could only do Table.

I am getting somewhere in that I did a transformation from text to numbers and the meter_readings are correct but not the timestamp ?

my timestamp is in format 1701767618

Not sure what to do ?

Thanks

32
321253×815 38.6 KB

6

First: make sure you are not usign table format as you did before

image
image1243×688 89.1 KB

Use also table view for debug:

image
image1503×491 32.8 KB

Column in table view have icon of types which was detected by Grafana:
image

But you can already CAST type on SQL level, e.g. SELECT column::INTEGER (so you don’t need transformation to change data type in Grafana).

Typical good writen query has also aggregation, e.g.

SELECT
  $__timeGroupAlias(time_column, $__interval),
  column_a::text AS "metric",
  AVG(column_b) AS "value"
FROM tables
WHERE $__timeFilter(time_column)
GROUP BY 1,2
ORDER BY 1

Check Grafana doc to know more about macros, e.g. $__timeGroupAlias in my example.

7

Hi,

Just a follow up … I did get this working, I improved my query.

I pieced together lots of small clues I found on the internet and came up with this that works !
Do I understand it … not at the moment ?
Any comments on the query welcome

Thanks for all your help.

My latest query

SELECT (energy.device_data->>'meter_reading')::float AS "Meter reading",
(energy.device_data->>'timestamp')::int AS Time
FROM energy WHERE device = 'IP22'
ORDER BY Time ASC ;
1 Like