Advanced Graphing (Part2): Visualize timeshift

#1

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.

3 Likes

Select specific day over last X years?
#2

Will this work with InfluxDB also?

0 Likes

#3

InfluxDB does not have a timeshift function :frowning:

0 Likes

#4

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

0 Likes

#5

Sadly only Graphite has timeshift integrated into its query analytics functions

1 Like

#6

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…

0 Likes

#7

Is it possible to de this with postgres backend ?

0 Likes

#8

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

little bit confused, can you please explain on this

0 Likes

#9

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.

0 Likes

#10

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

0 Likes

#11

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.

0 Likes

#12

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

0 Likes

#13

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

0 Likes