CODEWITHSUNDEEP
influxdb
Md Enayat
Md Enayat

Reputation: 167

How to retrieve the last data of 30day with last time from influxDB

I'm new in influxDb please help me with this question. So the question is

SELECT last(val1), last(val2) FROM powerandlevel where id='$id' and time>1606761000000000000+1d and time<1609093800000000000+1d group by time(1d, -60m-1s)

I hitted this above query and get result like this

time                 last  last_1
----                 ----  ------
2020-11-30T22:59:59Z 92.6  91.4
2020-12-01T22:59:59Z 95.3  94.2
2020-12-02T22:59:59Z 97.7  96.6
2020-12-03T22:59:59Z 101   99.9
2020-12-04T22:59:59Z 103.1 102.1
2020-12-05T22:59:59Z 105.2 104.1
2020-12-06T22:59:59Z 108   106.9
2020-12-07T22:59:59Z 110.5 109.4
2020-12-08T22:59:59Z 113.1 112.1
2020-12-09T22:59:59Z 117.5 112.8
2020-12-10T22:59:59Z 122.4 112.8
2020-12-11T22:59:59Z 127.1 112.8
2020-12-12T22:59:59Z 131.4 112.8
2020-12-13T22:59:59Z 134.8 114.4
2020-12-14T22:59:59Z 137.4 117.1
2020-12-15T22:59:59Z 140.4 120
2020-12-16T22:59:59Z 143.7 123.2
2020-12-17T22:59:59Z 146.8 126.6
2020-12-18T22:59:59Z 149.6 129.2
2020-12-19T22:59:59Z 152.3 132
2020-12-20T22:59:59Z 155.3 135.1
2020-12-21T22:59:59Z 158.6 138.3
2020-12-22T22:59:59Z 161.4 141
2020-12-23T22:59:59Z 163.6 143.3
2020-12-24T22:59:59Z 165.7 145.4
2020-12-25T22:59:59Z 167.9 147.5
2020-12-26T22:59:59Z 171.4 151.1
2020-12-27T22:59:59Z 173.6 153.4

but when I verified my result with this query

SELECT val1, val2 FROM powerandlevel where id='$id' and time>'2020-12-26T22:59:59Z' limit 10

time                     val1  val2
----                     ----- -----
2020-12-26T22:59:59.677Z 167.9 147.5
2020-12-26T23:00:00.678Z 167.9 147.5
2020-12-26T23:00:01.678Z 167.9 147.5
2020-12-26T23:00:02.679Z 167.9 147.5
2020-12-26T23:00:03.682Z 167.9 147.5
2020-12-26T23:00:04.681Z 167.9 147.5
2020-12-26T23:00:05.677Z 167.9 147.5
2020-12-26T23:00:06.676Z 167.9 147.5
2020-12-26T23:00:07.679Z 167.9 147.5
2020-12-26T23:00:08.677Z 167.9 147.5

now if you'll see the time 2020-12-26T22:59:59 in both result, you'll get get to know the both result have different value in time. Even you get this value 2020-12-26T22:59:59.677Z 167.9 147.5 in first result in time of 2020-12-25T22:59:59Z . I observed that my query data are correct but I'm getting the first time of that grouped data but I'm getting the last data of every day. Could you please tell me how to get the last time as like getting the last data value from DB.

Thankyou in advance.

Upvotes: 0

Views: 1034

Answers (1)

Jan Garaj
Jan Garaj

Reputation: 28626

You can get last value with LAST function, but you can't get timestamp of that last record - that's not supported, see https://github.com/influxdata/influxdb/issues/5793

BTW: group by time(1d) generates one value per day and it will have time 00:00:00. But you are applying also offset -60m-1s so that time is "moved" back to the previous day (22:59:59 previous day). It doesn't make sense to me.

Upvotes: 2

Related Questions