Hello
is it possible to display in one graph (x-axis from 1. Jan to 31. Dec) several years.
My data source is a mysql database but I was not able to querry values only for 2021 and another query for 2020 etc.
thanks
1 Like
Hello
is it possible to display in one graph (x-axis from 1. Jan to 31. Dec)
several years.
Yes, it is.
My data source is a mysql database but I was not able to
querry values only for 2021 and another query for 2020 etc.
Give us a clue as to what went wrong when you tried (and at least tell us how
you tried to do these queries), and we might be able to give you a clue as to
how to achieve this.
Antony.
Oh, wait - did you mean that you want an X-axis which simply displays “1st
Jan” to “31st Dec” without any years shown, and you want several years’ data
superimposed on top of each other in the graph?
The would be far more difficult - not so much in terms of getting the X-axis
right for the different data sets, but in terms of labelling which year’s data
is which data set.
I’m assuming that you have just one measurement in your database which covers
many different years.
Antony.
1 Like
Antony, exactly that is what I want and you are right with your assumption.
My thoughts were, I will do different queries with the time slot of 2021 / 2020 / 2022. This would make labeling easy, wouldn’t it?
1 Like
Is there a chance to do this?
1 Like
I do not know a way of superimposing data from different time ranges onto a
single X-axis. Hopefully someone else can suggest an idea.
Antony.
1 Like
Yes, it may be done but you will have to reformat the data so that the year is not present. So get the data, say by month for each year, and then reformat to remove year, and when you chart it will see the value by month and plot 2 graphs by month of the year.
What you want is a year-on-year comparison by either, month, quarter, week of year etc.
I would suggest you also use the Infinity data source to do this but whaq you are asking is not new.
You could do 2 queries one for each year and then join them by month of year for example.
If you do a single query for 2 years then you must reformat the date to remove years and without year the data will appear as an effective join.
see:
Compare SQL Charts To Previous Period | datapine.
How To Compare Different Periods With Time Charts Via SQL | datapine
1 Like
This may also be possible with Influx (using Flux). More here:
2 Likes
I have a similar request but using Prometheus TSDB as a data source. I would like to enter four UTC times and have a dashboard of the metrics for the two time spans overlaid with TS1 and TS2 prefixes on the labels.
If there is not a way to do this, I would like to start collecting other folks use-cases. In my case it if for comparison of performance benchmark runs. I know exactly when they start and stop and would like to compare various system metrics.
Just started my requirements document here:
I have achieved your objective by constructing the sql query in following way:
SELECT min(Day) time , SUM(CY-3) ‘2019’, SUM(CY-2) ‘2020’, SUM(CY-1) ‘2021’, SUM(CY) ‘2022’ FROM (
SELECT
Day, month(Day) ‘Maand’,
max(case WHEN year(Day)=year(CURRENT_DATE) then graad_dag else 0 END) ‘CY’,
max(case WHEN year(Day)=year(CURRENT_DATE)-1 then graad_dag else 0 END) ‘CY-1’,
max(case WHEN year(Day)=year(CURRENT_DATE)-2 then graad_dag else 0 END) ‘CY-2’,
max(case WHEN year(Day)=year(CURRENT_DATE)-3 then graad_dag else 0 END) ‘CY-3’
FROM hist_utils
WHERE year(Day)>year(CURRENT_DATE)-4
GROUP BY Day) as a
WHERE 1
GROUP BY Maand
ORDER BY Maand;
And the graph looks like this:
3 Likes
Great but mayx I ask where I have to paste these lines in? or can it be done by gui?
When you edit the panel in the GUI, you have the option of using building the query in a guided way or manual, choose manual and paste the query there. Would recommend to use phpmyadmin for testing the query for your data and then paste it there, saves difficult debugging and fixing. 
in these lines I am having issues. I have nearly no idea about SQL.
What does CY mean and the error message claims
/* SQL Fehler (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2021 ’,
SUM(CY) ‘ 2022 ’
FROM
(
SELECT
DAY,
M...' at line 3 */
the time entry of my DB looks like this:
2021-03-02 11:01:01.136
The CY fields come out of the sub-query:
SELECT
Day, month(Day) ‘Maand’,
max(case WHEN year(Day)=year(CURRENT_DATE) then graad_dag else 0 END) ‘CY’,
max(case WHEN year(Day)=year(CURRENT_DATE)-1 then graad_dag else 0 END) ‘CY-1’,
max(case WHEN year(Day)=year(CURRENT_DATE)-2 then graad_dag else 0 END) ‘CY-2’,
max(case WHEN year(Day)=year(CURRENT_DATE)-3 then graad_dag else 0 END) ‘CY-3’
FROM hist_utils
WHERE year(Day)>year(CURRENT_DATE)-4
GROUP BY Day
The primary query is based on the results of the sub-query, a so called nested query. Go to w3 schools (W3 schools MySQL training) for some training on MySQL programming
You want something like this ?
You can’t do it directly in Grafana.
The data from my smart meter is send to a MySql database. For this graph I use intermediate tables with the year totals like this.
Those tables are build automatically by a script from the recorded data once per day (during the night). Another advantage is that the heavy lifting is done outside grafana.
If you are Dutch (or can read Dutch) you can go to my blog where it is explained in more detail : https://blog.westerbeek.name/
I hope this helps.
Peter
1 Like
Hello all!
I’ve got a very similar question, so I feel it makes sense to attach it to this thread.
Right now, I am in an very early beginner stage regarding Grafana and playing around with my first diagrams.
I used to create graphs in Excel showing i.e. the total power consumption of my heat pump over the years like this:
I am very used to it as it gives a good impression how the actual power consumption is compared to the years before.
Now I want to get this in a Grafana dashboard with automated data logging (just set up in InfluxDB 2.7). The data is of course one dataset over all years containing the meter readings. Old values are logged at irregular points in time (imported via csv), new values (= absolute meter readings) come automated on a daily basis. This means I’ll need to do some math (or using increase()??) so that the 01.01 at 00:00 is always shown as 0 for each year and the subsequent values the delta.
Is that possible at all? If yes, can you help me and push me into the right direction how I can write an appropriate querry for Influx2 ?
Thank you in advance for your comments!
Edit: InfluxDB 2.7.0 and Grafana v9.4.7
It should be possible to compare year over year data using this.
Hi @grant2,
thank you for your reply! Short disclaimer: I am really a newbie and in the “hello world” phase learning flux.
I played around with experimental.alignTime and I believe this function is the key solving the problem. It shifts the data to any point of time.
from(bucket: "testbucket")
|> range(start: 2019-01-01T23:59:00Z, stop: 2019-12-31T23:59:59Z)
|> filter(fn: (r) => r["_measurement"] == "Bezugszaehler")
|> filter(fn: (r) => r["_field"] == "value")
|> experimental.alignTime(alignTo: 2000-01-01)
|> increase()
|> yield()
Now I need to repeat this - let’s say for the last 5 years. Can I use a for-loop for this? Or is there any other trick?
Hi all,
okay, I’ve figured out the next step:
import "experimental"
from(bucket: "testbucket")
|> range(start: 2019-01-01T00:00:00Z)
|> filter(fn: (r) => r["_measurement"] == "Bezugszaehler")
|> filter(fn: (r) => r["_field"] == "value")
|> window(every: 1y)
|> experimental.alignTime(alignTo: 2000-01-01)
|> increase()
This gives the curves as intended for each year since 2019. But: I cannot see which one is showing which year…
How can I get each curve (re)named to indicate the original year?
1 Like