5 ms·

It would be nice to have yearly, monthly, weekly and other recurring graph, I tried to hack it with sql window function, but it gives lot of problems. https://

by hokkos 6y ago

It would be nice to have yearly, monthly, weekly and other recurring graph, I tried to hack it with sql window function, but it gives lot of problems. https://community.grafana.com/t/yearly-monthly-weekly-and-other-recurring-graph/24998 https://community.grafana.com/t/yearly-monthly-weekly-and-ot...

errantspark 6y ago
I have this set up to compare my last three days of solar output. I don't remember off the top of my head how I did it but I might be able to help out. What DB are you running on top of? https://imgur.com/a/UXQ4bfY https://imgur.com/a/UXQ4bfY
- hokkos 6y ago
  I'm using Postgres, I managed to hack something to see yearly data, it works if it look at the full year, but it fails if I the zoom in graphana.
danlimerick 6y ago
There is the timeshift function in the query editor but that is per panel and not per query. So you would have to have a panel per year. It should be possible with SQL but it is trickier. TSDB's like Prometheus, Graphite and Influxdb (Flux) have the timeshift function built into their query languages. (https://community.grafana.com/t/advanced-graphing-part2-visualize-timeshift/365/11 https://community.grafana.com/t/advanced-graphing-part2-visu...)
- danlimerick 6y ago
  We are also working on an expression language to backfill other data sources. You can see the frontend version of this - transformations in 7.0. The backend version which will support functions like timeshift is in alpha for now. Hopefully it will be officially released in the near future.
deleted 6y ago
[deleted]
svenklemm 6y ago
To get graphs for this week and the last 3 weeks in the same panel you could use something like this: SELECT i::text as metric, l1.* FROM (SELECT g1 AS i, g1 * '1week'::interval AS interval, '1 week'::interval AS span, now() AS start FROM generate_series(0,3) g1) g1 JOIN LATERAL ( SELECT time + interval AS time, value FROM metrics WHERE time > start - (span+interval) AND time < start - interval ) l1 ON TRUE ORDER BY time;
akulkarni 6y ago
I'm not sure you need window functions for your query. Have you tried something like this? SELECT time, '2018' as metric, value FROM metrics WHERE time >= '2018-01-01' AND time < '2019-01-01' UNION ALL SELECT time - '1 year'::interval, '2019' as metric, value FROM metrics WHERE time >= '2019-01-01' AND time < '2020-01-01' ORDER by time; You will have a 2018 and a 2019 line if you plot this. (And you can of course make the time-periods dynamic.) Note: One of our (TimescaleDB) engineers authored and contributed the PostgreSQL data source to Grafana. We happen to see quite a few Grafana dashboards built with SQL. If you'd like to debug in real-time then I'd suggest joining our Slack: https://slack.timescale.com/ https://slack.timescale.com/.

by hokkos 6y ago

errantspark 6y ago

I have this set up to compare my last three days of solar output. I don't remember off the top of my head how I did it but I might be able to help out. What DB are you running on top of? https://imgur.com/a/UXQ4bfY https://imgur.com/a/UXQ4bfY

hokkos 6y ago
I'm using Postgres, I managed to hack something to see yearly data, it works if it look at the full year, but it fails if I the zoom in graphana.

danlimerick 6y ago

There is the timeshift function in the query editor but that is per panel and not per query. So you would have to have a panel per year. It should be possible with SQL but it is trickier. TSDB's like Prometheus, Graphite and Influxdb (Flux) have the timeshift function built into their query languages. (https://community.grafana.com/t/advanced-graphing-part2-visualize-timeshift/365/11 https://community.grafana.com/t/advanced-graphing-part2-visu...)

danlimerick 6y ago
We are also working on an expression language to backfill other data sources. You can see the frontend version of this - transformations in 7.0. The backend version which will support functions like timeshift is in alpha for now. Hopefully it will be officially released in the near future.

deleted 6y ago

[deleted]

svenklemm 6y ago

To get graphs for this week and the last 3 weeks in the same panel you could use something like this: SELECT i::text as metric, l1.* FROM (SELECT g1 AS i, g1 * '1week'::interval AS interval, '1 week'::interval AS span, now() AS start FROM generate_series(0,3) g1) g1 JOIN LATERAL ( SELECT time + interval AS time, value FROM metrics WHERE time > start - (span+interval) AND time < start - interval ) l1 ON TRUE ORDER BY time;

akulkarni 6y ago

I'm not sure you need window functions for your query. Have you tried something like this? SELECT time, '2018' as metric, value FROM metrics WHERE time >= '2018-01-01' AND time < '2019-01-01' UNION ALL SELECT time - '1 year'::interval, '2019' as metric, value FROM metrics WHERE time >= '2019-01-01' AND time < '2020-01-01' ORDER by time; You will have a 2018 and a 2019 line if you plot this. (And you can of course make the time-periods dynamic.) Note: One of our (TimescaleDB) engineers authored and contributed the PostgreSQL data source to Grafana. We happen to see quite a few Grafana dashboards built with SQL. If you'd like to debug in real-time then I'd suggest joining our Slack: https://slack.timescale.com/ https://slack.timescale.com/.