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;

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-visu...)

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.

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

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

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.