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.
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.
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)
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.
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.
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,
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)
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"].
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)…
Hi! i have a similar issue, the data that i have is in a tabular format and has 2 columns - one having time of a previous version and another having the time of new version. I need a 3rd column having a percentage change in the time. How can it be done?
Thanks!
