DATEADD function use to properly compare figures vs. Last Year

Grafana MySQL
,
#1

Dear Grafana community,

I am using Grafana v8.2.3 with MySQL data source for sales monitoring purposes.

When I select a time period, I can see the total sales over this period, no problem (this year, TY).
Then, I would like to compare these total sales vs. last year total sales (LY).

Currently, I am using the following query, which is fine and correctly returns result :

SELECT
    SUM(o.paid_amount_eur)
FROM
  orders o
WHERE
  o.paid_at BETWEEN FROM_UNIXTIME($__unixEpochFrom() - 31536000) AND FROM_UNIXTIME($__unixEpochTo() - 31536000)

I used this to retreat one year to the selected time frame (31536000 = 365 days in seconds).

However, I do not like this query because it does not take into account the number of days within the year. It means that if I compare 2020 vs. 2019, the result will be erroneous (because 2020 had 366 days).

This is why I would rather use the MySQL DATEADD() function, but I do not manage to do so.

I tried the following, but I get the following error : ‘db query error: query failed - please inspect Grafana server log for details’ :

SELECT
  SUM(o.paid_amount_eur)
FROM
  orders o
WHERE
  o.paid_at BETWEEN DATEADD(y, -1, $__timeFrom()) AND DATEADD(y, -1, $__timeTo())

I’ve seen the documentation here https://grafana.com/docs/grafana/v7.5/datasources/mssql/, but I still do not understand how can I use the DATEADD() function.

Any idea on how can I do this ?

Thanks a lot for your help in advance.
Cheers,

#2

Hi community,

Any idea on this one?

Cheers!