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/.
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/.