Advanced Graphing (Part2): Visualize timeshift

The Graphite time series query language supports time shifts, so you can compare one metric against itself but for a different time range.

For example in the graph above we show dashboards downloaded on grafana.com/dashboards grouped into 6 hour buckets. You can also see the series but last week so you can easily compare how the current values compare against the week before.

If you zoom out so the time range is bigger then the time shift you can see how the green line is mirrored in the faint time shifted series further along the graph.

To accomplish this visual look we need to use a series override.

The override sets a line fill and disables line width on the time shifted series. This creates the desired “shadow” effect for the time shifted data.

4 Likes

Will this work with InfluxDB also?

1 Like

InfluxDB does not have a timeshift function :frowning:

Is there any way to create such graph (with charts from two time intervals on a single graph) with Elasticsearch used as data source?

Sadly only Graphite has timeshift integrated into its query analytics functions

1 Like

And now? elasticsearch implemented the timeshift function? I found something called meta queries (https://github.com/GoshPosh/grafana-meta-queries), i’ll do a test…

Is it possible to de this with postgres backend ?

is downloaded - 7d is a separate alias or downloaded (alias name ) - 7 days ??

little bit confused, can you please explain on this

only Graphite has timeshift integrated into its query analytics functions

prometheus also has it: (downloaded offset 1d).
I also sometimes use something like this:
clamp_min(downloaded - (downloaded offset 1d), 0) on the right-Y axe. Can’t say its that useful but it is what it is.

Hi folks,

Is it possible to generate a query to be plotted like that with MySQL?

Im currently using a custom plug in I found called Graph Compare Panel, that basically do it, but i guess then it can be simply done using a query, not sure how?

This custom plugin is with bugs that make it unstable (basically cant allow to override or change color or type of line)

Thanks

jp

FYI, time-shifting a series using the postgres plugin is very easy, I suspect it’s exactly the same for mysql. When you build your query, where you fill in the column name for the timestamp simply change it to an expression. For example, my column is called ts and in order to get yesterday’s data I simply change to ts + interval '24 hours'. I have one graph where I’m plotting the current temperature and I overlay yesterday’s so I can compare:


It looks shitty due to a data gap, but the yellow (yesterday) trace is a repeat of the green segment at the left. The X axis here shows 48 hours for illustration purposes.
The data source looks as follows:

Series A is for today and series B is time-shifted by 24 hours. Note that you can’t do the “group by” on the time-shifted data using the query builder, it messes up.

Hi tve, thanks for your detailed answer,

Though it seems

  1. its not working with the <timestamp> + (<operation> <time range>) clause in MySQL, just tried different ways and exactly as you suggest but it doesnt do it.

  2. if you show a 2 days time range and compare both days in the same graph, its not exactly what im looking for: to be more precise, i have an average hourly traffic rate (measured in mps ‘messages per sec’), and i report it in a weekly range,

4/2 to 9/2 week

hence what im looking is to get the past 15days-week MPS line (ie 27/1 to 4/2), BUT in the same time range as last week (, to show the last last 4/2 to 9/2 week mps line to compare),

a plugin does the trick, but it has several bugs and i need to do the same in a simple query (if doable)

this is what plug is does, and i need:

so how I do overlap both time lines in the same graph with MySQL queries without using that custom plugin (again, several bugs),

Thanks a lot for your advising,

j

Does ElasticSearch have timeshift function yet? If yes, is it implemented in the same way?

Hi jpbrunori, can you tell me what plugin are you using? I need this trick too.

Hi there, dear @voiprodrigo,

regarding …

… we just wanted to let you know that

Documentation

TimeShift

TimeShift adds a fixed duration to time columns.
The output table schema is the same as the input table.
If the time is null, the time will continue to be null.

TimeShift has the following properties:

Name Type Description
duration duration Duration is the amount to add to each time value. May be a negative duration.
columns []string Columns is a list of all columns that should be shifted. Defaults to ["_start", "_stop", "_time"].

Example:

from(bucket: "telegraf/autogen")
	|> range(start: -5m)
	|> timeShift(duration: 1000h)

https://docs.influxdata.com/flux/v0.24/functions/built-in/transformations/timeshift/
https://github.com/influxdata/flux/blob/master/docs/SPEC.md#timeshift

Testimonials

We have been able to conduct successful trials using the new timeshift functionality through InfluxDB (Flux) Datasource [BETA]. If we got you interested in this, you might also enjoy reading (partially in German)…



Happy graphing and with kind regards,
Andreas.

1 Like

Great update, thanks!