Extract first time field from a query and add it to the time fields of a second query. aka Time shift

Dear Grafana folks,

We have dynamic datasets for short time periods that we select per subject.
They occur at arbitrary times. These are from a standard datasource query and lets call them A.

Our use case is that we want to apply a specific and static data set as an overlay over A.

We made a query B of this static data with Marcus Olsson’s nice CSV datasource.

You can see the panel at the top of this dashboard called “Time Shift Me”.

https://humanalyse.com/d/a_R5qqEVz/timeshift?orgId=207&from=1681801860000&to=1681816500000&var-subject=039uk@meterbolic.org&var-glucose_units=mMol&var-insulin_units=µIU%2Fml

We tried various transforms of the Series B following the idea to take the first time point of Series A and adding this number to each of the time fields of Series “B”. This was not successful :man_shrugging:

The idea is to put the static data in the csv starting at 0 seconds in the Epoch.
Then shift it so that both A and B have the same starting time.kraft1.csv

FWIW, the CSV is currently defined with a static time, this is so that it is visible on the panel, but this is not how it is intended to be:

time,kraft1
2023-04-18 07:51:00,1
2023-04-18 08:11:00,3
2023-04-18 09:11:00,6
2023-04-18 10:11:00,2

And we could use Epoch seconds for our time fields and queries as required.

Would love to see how this can be done or better a working example to copy and paste :wink:

Love Grafana, thanks to the devs, thanks to the generous community.

Warm wishes
Eric

Here is the panel with the CSV in B