Select 1 time period, "freeze"/"remember" it, select another, show both on a graph

For the context: Grafana Enterprise 10.2.3 inside Docker container on Ubuntu server + PostgreQSL database

Prerequisites:

I have a timeseries graph panel that shows aggregated data for multiple entities.
The panel is linked to dashboard time-selector.
The panel is linked to dashboard variable (list of sources, multiple selected).

What am I trying to achieve:

  1. Lets say I selected Entity A and time-range of 2024-02-13 - 2024-02-17. The graph should look something like this:

  2. Now I want this data (Entity A from 13th to 17th February) to be put against data for Entity B but for another time period. Let’s say I select Entity B and period from 13th to 17th November 2023. I want this data to end up on the same graph (somehow), so the output would look something like this:


*yellow being the data for Entity B for completely different period

The way I want it to work is to be able to select the first set of data, then completely another one and then show 2 on the same graph (not sure if it’s possible).

I don’t really care about the fact that technically the data for Entity B on the graph aligns with X-axis which is period for Entity A data (or similar matters). The use case is pretty simple: entities are being affected by some external factors; each entity is affected separately at different moment; I need to see the pattern and compare visually how they handle it (e.g. how fast it recovered)

Any advice is welcome. Thanks!

I would use 2 queries, where you can play with time column on the sql level, so that historic data will be in the panel time range and not outside, e. g. 1 year shift (just exampleof idea, not real Postgresql syntax):

SELECT
  timecolumn+1year AS time, 
  ...
WHERE
  timecolumn BETWEEN from-1year AND to-1year
1 Like

This is more pg sql query question than a grafana question

That said I would leverage common table expression with UNION approach

With cte
As
(
Select metric+ year_month as metric, value, time 
From table t
Where time between $from and #to. --this year

)
Select ly.metric + ly.year_month as metric, ly.value, cte.time
From table ly
--or cross apply
Join cte on ly.month_date= cte.month_date
Where time between $fromLastYear and $toLastYear
Union
Select metric, value,time 
From cte

By joining on month_date you fake align the dates of this year and last, then use this year time for time series plotting.

This is more ms sql flavor syntax but you get the idea?
This is rough draft not in front of pc

1 Like

@jangaraj @yosiasz Thanks for your replies! As I understand, both solutions suggested rely heavily on pre-defining some fixed value of time offset.

That would allow to compare April of 2024 to April of 2023, for example, which this isn’t exactly what I am trying to achieve.

What I am trying to achieve is selecting two completely independent unrelated periods, for example:

  • from 1st to 12th of May 2024
  • from 12th to 23rd of January 2018
    (both 11 days but not by a fixed offset)

And putting these two time-series on the same graph. I have a suspicion that’s not how Grafana works, but maybe there is a workaround or some plugin?

No, you can move any time range.

1.)

SELECT
  timecolumn + <timeshift, so selected timerange will be "moved" to panel timerange>
...
WHERE 
  timecolumn BETWEEN 1st of May 2024 AND 12th of May 2024

2.)

SELECT
  timecolumn + <timeshift, so selected timerange will be "moved" to panel timerange>
...
WHERE 
  timecolumn BETWEEN 12th of January 2018 AND 23rd of January 2018

Yes, you can be complaining that you need a math to calculate that timeshift, but it can be “automated” on the SQL level/dashboard variables, …
Yes, you can be complaining that’s not comfortable and you are correct. Grafana was not designated for this kind of comparative visualization, but you can write own Grafana plugin, which will provide more comfort for this particular use case.

1 Like

The query above should work in that case if you use cross apply but you will need a custom second time picker for query B. Use out of the box time picker for query A