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-ot...
https://community.grafana.com/t/yearly-monthly-weekly-and-ot...
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;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-visu...)
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/.