fantastic job with this application, it is getting better a better by the day. I would like to display in the same graph this month and last year month. I was thinking to create two queries, Query A for this month and query B for last year month. Unfortunately I’m having issues with last year month. The query should be based on the ranges that I select and subtract by a a year. The first query “A” I have is something like this, how do a create another query “B” to dispay last year data?
UNIX_TIMESTAMP(datecreated) as time_sec,
max(devicevalue) as value,
‘KWH’ as metric
WHERE $__timeFilter(datecreated) and deviceroom = ‘Energy Monitor’ and devicename = ‘Total KW Hours’
group by HOUR(datecreated)
If you’re using mysql 8 or later you may be able to use windowed function. Otherwise think you need to filter time using the __timeFrom/__timeTo instead of $__timeFilter. Then you should be able to use mysql builtin datetime function to timeshift back 1 year in your second query.
UNIX_TIMESTAMP(DATE_ADD(datecreated, INTERVAL 1 YEAR)) as time_sec,
datecreated >= DATE_ADD($__timeFrom(), INTERVAL -1 YEAR) AND
datecreated <= DATE_ADD($__timeTo(), INTERVAL -1 YEAR)
ORDER BY ...
Ideally I would like to display something similar to this, sorry if I sound confusing. If I create a query A with this year query and query B with the query you have outline, Grafana will display “Data points outside time range” if I add a time shift of one year than it will not show this year.
The custom range is from 2018-01-01 to 2018-11-26. Sorry about the confusion so far.
@mefraimsson, I will try it,alghouth I think the issue is doing the quey and selecting the date range that is outside that query range, this is what I normally get when I run the query outside these ranges. Grafana will display “Data points outside time range
@gaurav24, this looks interesting, I will try it this weekend. From the reading, it sounds like it will work with Mysql, correct?
Your using the Meta Queries plugin. I cannot help you with that since it’s a plugin (not included in Grafana core). I would suggest you first try and get a working query using only your mysql datasource.