# How to shift date of data and how to set per-graph time range?

Hello everyone
I am new to Grafana and am loving it so far. Relatively easy to use and get into and looks great.

There are two small issues I am facing.

1. How can I shift the data by -24 hours? I have data which is being reported as â€śconsumption yesterdayâ€ť, so effectively I am seeing the power consumption of the previous day (this makes sense because live daily consumption is not much good given that you wonâ€™t have all values until midnight). I now want to shift the data so it is shown on the correct day.
Data shown on Tuesday is Mondays data and so on.

2. When I define the time range of a graph, this is also applied to all other graphs. I would like to have daily graphs, weekly graphs etc. Is it possible to define the time range on a per-graph basis?

Thank you all for your feedback
Alex

1. How can I shift the data by -24 hours? I have data which is being
reported as â€śconsumption yesterdayâ€ť, so effectively I am seeing the power
consumption of the previous day (this makes sense because live daily
consumption is not much good given that you wonâ€™t have all values until
midnight). I now want to shift the data so it is shown on the correct day.
Data shown on Tuesday is Mondays data and so on.

Please show us the query youâ€™re using and show us a sample of the data in your
data store which contains the daily values.

1. When I define the time range of a graph, this is also applied to all
other graphs. I would like to have daily graphs, weekly graphs etc. Is it
possible to define the time range on a per-graph basis?

Yes, you can set â€śRelative Timeâ€ť and â€śTime Shiftâ€ť indivudally per panel.

Antony.

Thank you for your feedback, @pooh

I have a fresh setup, so not really much data.

Essentially it is right now
X1 = 07.06.2021
Y1 = 0.456 kWh
X2 = 08.06.2021
Y2 = 0.656 kWh
â€¦
but in reality, X1 should be 06.06.2021 and X2 should be 07.06.2021 because the value is yesterdayâ€™s value. So date of recording is today but date of origin is yesterday.

I think there is a misunderstanding, maybe. Time shift and relative time do not define the x-axis range to display.
So, letâ€™s say I want to see Monday - Sunday on one graph but I want to see the last 24 hours on another graph. How to I define these two x-axis time ranges for the two graphs?

I have a fresh setup, so not really much data.

Essentially it is right now
X1 = 07.06.2021
Y1 = 0.456 kWh
X2 = 08.06.2021
Y2 = 0.656 kWh
â€¦
but in reality, X1 should be 06.06.2021 and X2 should be 07.06.2021 because
the value is yesterdayâ€™s value. So date of recording is today but date of
origin is yesterday.

I would very much recommend that you fix the source of data so that it puts
correct datestamps for each value into your data store. I canâ€™t recommend how
to do this in detail without knowing what language your script for collecting
this data and putting it into your data store is written in, but basically you
want to do some equivalent of â€śsubtract_date(today, 1 day)â€ť before putting the

Yes, you can set â€śRelative Timeâ€ť and â€śTime Shiftâ€ť indivudally per panel.

I think there is a misunderstanding, maybe. Time shift and relative time do
not define the x-axis range to display.

Um, yes they do?

So, letâ€™s say I want to see Monday - Sunday on one graph but I want to see
the last 24 hours on another graph. How to I define these two x-axis time
ranges for the two graphs?

â€śMonday to Sundayâ€ť is not clear to me. Do you mean â€śthe current weekâ€ť or
perhaps â€śthe previous weekâ€ť? Or maybe something else?

You cannot get Grafana to display a composite of all weeksâ€™ data overlaid on
one another, just in case that is what you meant.

Assuming youâ€™d like one graph to show the previous day, and another graph to
show the previous week, I think you would use:

1. Relative time = now/d, Time shift = 1d/d

2. Relative time = now/w, Time shift = 1w/w

Quoting from https://grafana.com/docs/grafana/latest/panels/queries/
#query-options :

"Relative time - You can override the relative time range for individual
panels, causing them to be different than what is selected in the dashboard
time picker in the top right corner of the dashboard. This allows you to show
metrics from different time periods or days on the same dashboard.

"Time shift - The time shift function is another way to override the time
range for individual panels. It only works with relative time ranges and
allows you to adjust the time range.

â€śFor example, you could shift the time range for the panel to be two hours
earlier than the dashboard time picker. For more information, refer to Time
range controls.â€ť

I hope that helps,

Antony.

Hello @pooh

I would very much recommend that you fix the source of data

The input data is supplied by a sensor. So the sensor tells me how much power was consumed the previous day. I want to use this available data. There is no fixing it because the sensor itself is â€śenergy yesterdayâ€ť.

â€śMonday to Sundayâ€ť is not clear to me.

Instead of Relative Time 14 days (which on a Wednesday would show Thursday two weeks ago until today, Weddesday) I want to see calendar weeks. So always Monday until Sunday, where each future day of the week would be empty. So I would see last week Monday until this week Sunday.

Thank you again for your help!
Alex

Hello @pooh

I would very much recommend that you fix the source of data

The input data is supplied by a sensor. So the sensor tells me how much
power was consumed the previous day. I want to use this available data.
There is no fixing it because the sensor itself is â€śenergy yesterdayâ€ť.

So, whatâ€™s the full path for the flow of data between sensor and your data
store (and for that matter, what are you using as your data store)?

â€śMonday to Sundayâ€ť is not clear to me.

Instead of Relative Time 14 days (which on a Wednesday would show Thursday
two weeks ago until today, Weddesday) I want to see calendar weeks. So
always Monday until Sunday, where each future day of the week would be
empty. So I would see last week Monday until this week Sunday.

In that case I think this â€śthis weekâ€ť should fit what you want for that.

Antony.

Oh, I think you meant two weeks, not just one, and there isnâ€™t anything
obviously labelled â€ścurrent fortnightâ€ť

Time range controls | Grafana Labs should
point you in the right direction, though - try â€śnow-2w/wâ€ť and â€śnow/wâ€ť.

Antony.

So, whatâ€™s the full path for the flow of data between sensor and your data
store (and for that matter, what are you using as your data store)?

Sensor â†’ MQTT â†’ Home Assistant â†’ InfluxDB â†’ Grafana

Oh, I think you meant two weeks, not just one, and there isnâ€™t anything
obviously labelled â€ścurrent fortnightâ€ť

Time range controls | Grafana Labs should
point you in the right direction, though - try â€śnow-2w/wâ€ť and â€śnow/wâ€ť

This is why this is tricky.
Today is Wednesday, 09.06.2021.
So â€śnow-2w/wâ€ť would show 24.05.2021 - 09.06.2021. So it is the same as selecting simply 2w = last 14 days.

But what I want is 31.05.2021 - 13.06.2021, which is Monday 31.05. until Sunday 13.06. This equals two full calendar weeks.
So the formula would need to be something like â€ślast week Monday until this week Sundayâ€ť.

And I want to do the same for daily views. I donâ€™t want to see the last 24 hours, I want to see from 00:00 oâ€™clock until 23:59 oâ€™clock, so one full calendar day.

Sensor â†’ MQTT â†’ Home Assistant â†’ InfluxDB â†’ Grafana

Iâ€™m not familiar with Home Assistant, but does it have any data manipulation
functions which would enable you to subtract one day from date on the way
between MQTT and InfluxDB?

Oh, I think you meant two weeks, not just one, and there isnâ€™t anything
obviously labelled â€ścurrent fortnightâ€ť

https://grafana.com/docs/grafana/latest/dashboards/time-range-controls
should point you in the right direction, though - try â€śnow-2w/wâ€ť and
â€śnow/wâ€ť

This is why this is tricky.
Today is Wednesday, 09.06.2021.
So â€śnow-2w/wâ€ť would show 24.05.2021 - 09.06.2021. So it is the same as
selecting simply 2w = last 14 days.

No, that is not what the â€ś/wâ€ť on the end of â€śnow/wâ€ť means.

And I want to do the same for daily views. I donâ€™t want to see the last 24
hours, I want to see from 00:00 oâ€™clock until 23:59 oâ€™clock, so one full
calendar day.

Look at the link I posted above.

Note the difference between â€śThis weekâ€ť and â€śThis week so farâ€ť, and between
â€śThis monthâ€ť and â€śThis month so farâ€ť.

You want the versions without â€śso farâ€ť in them.

Antony.

I must have somehow messed up because I tried â€śnow-2w/wâ€ť and it gave the wrong result. Or rather I thought I had applied â€śnow-2w/wâ€ť!
I tried again and it worked, thank you!

One question, because I seemingly canâ€™t find it.
How do it use â€śRelative Timeâ€ť for â€śFromâ€ť â€śToâ€ť? I see that I can do this for absolute time at the top of the panel but this is then applied to all panels.
Relative time only seems to allow one statement, which would be e.g. â€śnow-2w/wâ€ť but then it ends today.
I wanted to try â€śFrom:now-2w/w To:now/wâ€ť but it does not work.

Iâ€™m not familiar with Home Assistant, but does it have any data manipulation
functions which would enable you to subtract one day from date on the way
between MQTT and InfluxDB?

It should be possible, yes, but this would mean that I have to create a new sensor for each senor I own. This new sensor would then be a manipulated version of the original.
This would create a lot of additional sensors that I donâ€™t really want.
If Grafana allowed me to simply shift the data, then I could cone the graph and use it for the different, already existing sensors.

Maybe it is not relevant anymore and/or I have missede something about the question, but I would use |> timeShift(duration:-1d) in the Flux query.

https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/timeshift/

Hi, you also have a look at the shift-selector panel plugin