-
What Grafana version and what operating system are you using?
8.0.3, ubuntu 20.04 -
What are you trying to achieve?
Get the schema right to display the state timeline for different devices correctly -
How are you trying to achieve it?
SELECT
bucket AS “time”,
device_id AS metric,
status
FROM device_status
ORDER BY 1,2 -
What happened?
- What did you expect to happen?
My table looks as below:
device_id | bucket | status
--------------+---------------------+--------
1 | 1970-01-01 01:22:30 | ACTIVE
1 | 1970-01-01 01:22:25 | ACTIVE
1 | 1970-01-01 01:22:45 | ACTIVE
1 | 1970-01-01 01:22:40 | ACTIVE
2 | 1970-01-01 01:22:35 | ACTIVE
2 | 1970-01-01 01:22:05 | ACTIVE
2 | 1970-01-01 01:22:20 | ACTIVE
2 | 1970-01-01 01:22:00 | ACTIVE
1 | 1970-01-01 01:22:15 | ACTIVE
1 | 1970-01-01 01:22:10 | ACTIVE
I think the state timeline is expecting data like this:
bucket | device1 | device2
-------------------------------+----------+--------+------
1970-01-01 01:22:30 | ACTIVE| ACTIVE
1970-01-01 01:22:25 | ACTIVE| ACTIVE
1970-01-01 01:22:45 | ACTIVE| ACTIVE
1970-01-01 01:22:40 | ACTIVE| ACTIVE
1970-01-01 01:22:35 | ACTIVE| ACTIVE
1970-01-01 01:22:05 | ACTIVE| ACTIVE
1970-01-01 01:22:20 | ACTIVE| ACTIVE
1970-01-01 01:22:00 | ACTIVE| ACTIVE
1970-01-01 01:22:15 | ACTIVE| ACTIVE
1970-01-01 01:22:10 | ACTIVE| ACTIVE
But I’m using Postgres, and to do a pivot table like this, I have to use crosstab() and hardcode the output columns. Can I use a data transformation do to this please?
-
Can you copy/paste the configuration(s) that you are having problems with?
-
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
-
Did you follow any online instructions? If so, what is the URL?
/docs/grafana/next/visualizations/state-timeline/