Currently I can query with “select sum(value) from device_frmpayload_data_meter where time > now() - 1w group by time(1d)” but because of the “now()” first value is alwasy changing. If I use limit 7, it starts limiting from beginning but I need latest records.
How can I select latest 7 days, or ignore first value on selection ?
This is what I understood.
You need to represent a week of data, with a point for each day.
The latest value (or current day) is always changing, because of the continuous input of data.
To exclude the last day using “LIMIT” you just need to add sorting, so something like:
SELECT mean("FreeSpace(%)") AS "mean_FreeSpace(%)" FROM "monitoring"."long"."ServerDiskSpace" WHERE time > now()-1w GROUP BY time(1d) FILL(previous) ORDER BY time ASC LIMIT 7
#which will return something like this: (now() = 2019/12/20 14:00:00)
time | mean_FreeSpace(%)
2019-12-13T00:00:00.000Z | 0.7447625
2019-12-14T00:00:00.000Z | 0.7447625
2019-12-15T00:00:00.000Z | 0.7446625
2019-12-16T00:00:00.000Z | 0.7447
2019-12-17T00:00:00.000Z | 0.744585714
2019-12-18T00:00:00.000Z | 0.744642857
2019-12-19T00:00:00.000Z | 0.744714286
But a problem still persists, the last day has been excluded, but because of the “now()-1w” you may get points which are acutally 8 days old, in this case 2019-12-13.
Using a subquery to limit the result with opposite sorting is not possible, ie:
SELECT * FROM ( <Previuos Query>) LIMIT 6 ORDER BY time ASC
# This will result in an error
To solve this you may set up a continuous query, that reads and store the data aggregated by day and runs N times a day (ie: run every 6hrs and calculate the whole day average, overriding the previous value).
By doing this you will have a value for each day, and the values of the last day will be updated only at the desired frequency.
Thanks for your reply, but the problem is, first value is alwasy changing with time, I am getting
2019-12-16T00:00:00Z 1530
but actually it must be
2019-12-16T00:00:00Z 1870
When I get values with ;
SELECT sum(value) FROM device_frmpayload_data_WaterMeter6 WHERE time > now()-1 w GROUP BY time(1d) FILL(previous) ORDER BY time ASC LIMIT 7
name: device_frmpayload_data_WaterMeter6
time sum
2019-12-16T00:00:00Z 1530
2019-12-17T00:00:00Z 1840
2019-12-18T00:00:00Z 2340
2019-12-19T00:00:00Z 2520
2019-12-20T00:00:00Z 2510
2019-12-21T00:00:00Z 2610
2019-12-22T00:00:00Z 2550
select sum(value) from device_frmpayload_data_WaterMeter6 group by time(1d) order by time
name: device_frmpayload_data_WaterMeter6
time sum
2019-12-12T00:00:00Z 580
2019-12-13T00:00:00Z 2780
2019-12-14T00:00:00Z 2340
2019-12-15T00:00:00Z 2280
2019-12-16T00:00:00Z 1870
2019-12-17T00:00:00Z 1840
2019-12-18T00:00:00Z 2340
2019-12-19T00:00:00Z 2520
2019-12-20T00:00:00Z 2510
2019-12-21T00:00:00Z 2610
2019-12-22T00:00:00Z 2550
2019-12-23T00:00:00Z 870
Now I understood the problem.
The time window is always moving (as it should) and therefore the data in the oldest day gets excluded as time pass.
ie:
- Now = 2019-12-23 10:00am → now - 1w = 2019-12-16 10:00am
- Now = 2019-12-23 12:00am → now - 1w = 2019-12-16 12:00am
- Now = 2019-12-23 14:00am → now - 1w = 2019-12-16 14:00am
You can manually replicate the issue using an absolute time filter:
{...} WHERE time > '2019-12-23 10:00:00' - 1w GROUP BY time(1d) ORDER BY time
{...} WHERE time > '2019-12-23 14:00:00' - 1w GROUP BY time(1d) ORDER BY time
IMO the best way to solve this is to use a continuous query to store the daily aggregated value in a different measurement and run it once a day (since you don’t use the value of the current day).
The continuous query will “set” the time of the data point at the beginning of the group by interval
ie:
GROUP BY time(1d) → will set the time of the calculated point to YYYY-MM-DD 00:00:00
you will the be able to get the last 7 days by:
# simpy use limit 7, since you already have one data point per day... you can add GROUP BY time(1d) but the result won't change
SELECT {...} FROM _AggregateMeasurement_ FILL(previous) ORDER BY time DESC LIMIT 7
# filter by time
SELECT {...} FROM _AggregateMeasurement_ WHERE time > now()-8d FILL(previous)
#Note that using now()-8 days will always return 7 points (days) except at exactly 00:00, in which it will return 8 data points, you can also use limit to avoid this
The query used in the continuous query will be the one you already have:
SELECT sum(value) FROM device_frmpayload_data_WaterMeter6 GROUP BY time(1d) FILL(previous)
I have now problem with last day data it can be changed by time and I need it to. If I use “Limit” it starts limiting from beginning and still first data is stays same.
I cant also use absolute time filter because the query is used at grafana to monitor last 7 days data and users cant change this.
I oredered with DESC to limit and I get the result that I want. But, the first row is current day of week and it goes with decreasing and it is ugly. For example. Today, yesterday, 2 days ago, … but I want to get it as … 2days ago, yesterday, today.
What did you do exactly? have you made a continuous query?
About this:
I oredered with DESC to limit and I get the result that I want. But, the first row is current day of week and it goes with decreasing and it is ugly. For example. Today, yesterday, 2 days ago, … but I want to get it as … 2days ago, yesterday, today.
Just change the sorting in the grafana column (if you are using the table visual).
Yes, Grafana continuously get query result and it is real time screen of total water consuption of days (today current consuption included).
On grafana there is no feature to show data reverse, even if it has this feature, the value of 7 days ago still will change with time now(). I must limit the data from reverse to beginning.
