Comparing different years in one graph from one data source

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.

This is what I tried. And I have no idea what I should add where …

Thanks, sorry I am really lost.

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:

1 Like

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:

2 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. :slightly_smiling_face:

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