Title
h
Hexory
05/04/2023, 8:50 PMHi,
I have a datapoint table created like this:
CREATE TABLE Datapoint (
device_id LONG,
sensor_id LONG,
timestamp TIMESTAMP,
value DOUBLE
) timestamp (timestamp) PARTITION BY YEAR;SELECT
timestamp as time,
sensor_id,
avg(value) as value
FROM Datapoint
WHERE $__timeFilter(timestamp) AND device_id=0
SAMPLE BY $__interval;g
Gabriel Mocan
05/04/2023, 9:08 PMCreate a variable for
device_idSELECT DISTINCT device_id FROM DatapointSELECT
$__time(timestamp),
sensor_id as metric,
avg(value) as value
FROM Datapoint
WHERE $__timeFilter(timestamp) AND device_id in ($device_id)
SAMPLE BY $__interval;h
Hexory
05/04/2023, 9:16 PMthanks for the reply, trying that solution, i'm getting
pq: cannot compare TIMESTAMP with type CHARAnd i'm looking to have one series per sensor_id. and then you pick your device in the variable dropdown.
g
Gabriel Mocan
05/04/2023, 9:20 PMThis query should plot a different line for each sensor_id.
What version of Grafana you’re on?
h
Hexory
05/04/2023, 9:24 PMv9.3.8
g
Gabriel Mocan
05/04/2023, 9:24 PMTry to build the query in data explorer section, using the query builder
h
Hexory
05/04/2023, 9:25 PMthe query builder is just empty. No table found.
g
Gabriel Mocan
05/04/2023, 9:25 PMYou selected the proper datasource?
h
Hexory
05/04/2023, 9:25 PMSELECT
$__time(timestamp),
sensor_id as metric,
avg(value) as value
FROM Datapoint
WHERE $_timeFilter(timestamp) AND device_id = 0
SAMPLE BY $__interval;even with this where device_id = 0, gives me nothing
g
Gabriel Mocan
05/04/2023, 9:26 PMWell we should see something on data explorer. What do you see on QuestDB UI, can you show a print from
select * from Datapointh
Hexory
05/04/2023, 9:27 PMimage.png
image.png
g
Gabriel Mocan
05/04/2023, 9:29 PMQuestDB runs postgres wire protocol on port 8812
Did you change that?
h
Hexory
05/04/2023, 9:30 PMnoo, well.
it runs inside my kubernetes cluster with a nodeport, but its connec ted to 8812
and I am getting data
g
Gabriel Mocan
05/04/2023, 9:31 PMweird because your datasource config on grafana is pointing to localhost:5432
h
Hexory
05/04/2023, 9:31 PMimage.png
nah, ive just removed it to take the screenshot. for "security" that was the placeholder
g
Gabriel Mocan
05/04/2023, 9:31 PMoh okay
did you try “sensor_id as metric” ?
In that same query you sent
h
Hexory
05/04/2023, 9:32 PMyea, doesnt change anything except having a metric label
I tried group by, but without success
g
Gabriel Mocan
05/04/2023, 9:33 PMimage.png
Example from chart here
Can’t see too much difference
h
Hexory
05/04/2023, 9:36 PMhummm, so metric is a special keyword for grouping? ill try googling it.
g
Gabriel Mocan
05/04/2023, 9:37 PMRefer to Grafana’s official docs.
https://grafana.com/docs/grafana/latest/datasources/postgres/
h
Hexory
05/04/2023, 9:57 PMgot some progress
image.png
soo combining my two columns was the trick
but got conversion issues for the In
voila, this works
SELECT
$__time(timestamp),
avg(value) AS value,
device_id || sensor_id AS metric
FROM Datapoint
WHERE $__timeFilter(timestamp) AND cast(device_id as String) in ($device_id)
SAMPLE BY $__interval FILL(0)g
Gabriel Mocan
05/04/2023, 10:02 PMNice