Group by only Date || Table || Grafana || influxDB

Grafana InfluxDB
1

Dear All,

I am using InfluxDB as a data source with Grafana.

I am trying to see the counts of last 7 days. When i set the time to LAST 7 Days. I can see the data in table.

02%20PM
02%20PM2856Ă—1274 323 KB

But, As you can see the above screenshot. Dates are being show with 5:30 hour added to date. I want the grouping to be done only based on DATE. I have time in IST format in my influxdb. It is considering the day to be from 5:30 hours to next day 5:30 hours. I want it to read 00:00 IST to next day 00:00 IST

Please suggest if i can get the data based on DATE alone with IST format to influxdb query.

Thanks in Advance.

2
3

Thanks jangaraj,

But still, Is there any way to achieve this?

4

Yes, use mentioned TZ() function in your raw queries.

5

Sorry for the delayed reply Jangaraj.

Is this the way you are talking about?

SELECT mean(value) FROM cpu WHERE time > now() - 7d GROUP BY time(1d) TZ(“America/New York”)

I have been trying to search for it. But no clue :frowning:

Please help with an example.

6 
> SELECT MEAN(availability) FROM "measurement" WHERE time>NOW()-2d GROUP BY time(1d)
name: measurement
time                mean
----                ----
1531267200000000000 99.74510503038258
1531353600000000000 99.17735011896465
1531440000000000000 99.20077320545704
> SELECT MEAN(availability) FROM "measurement" WHERE time>NOW()-2d GROUP BY time(1d) TZ('US/Pacific')
name: measurement
time                mean
----                ----
1531292400000000000 99.47611957508033
1531378800000000000 99.24605839328007
1531465200000000000 99.19746505567402
> SELECT MEAN(availability) FROM "measurement" WHERE time>NOW()-2d GROUP BY time(1d) TZ('US/Eastern')
name: measurement
time                mean
----                ----
1531281600000000000 99.71434604600964
1531368000000000000 99.21372558021126
1531454400000000000 99.18848287561322
1 Like
7

Thanks a lot for your Jangaraj,

It works well. I am glad to have some results and all credit goes to you.

But, I can still see some gap in this. Last day of the group by data is having wrong information. I tried with NOW()-2d and NOW()-10d

Last day of the group by is having wrong data.

07%20PM
07%20PM2836Ă—1092 393 KB

if you look into this screenshot. 3rd of July is having data of 651.20K only. But the actually counts for that day are according to the trending which is near by 1.80Mil

56%20PM
56%20PM2816Ă—192 13.6 KB

See, this is the correct count for that day.

Any clue why it’s calculating wrong data for last day of group by?

8

I don’t know - use https://community.influxdata.com/ for InfluxDB specific question. My guess: problem is in your day vs daytime date values.

9

Thanks Jangaraj for all your help.

it is querying the data correctly. But the issue is with only LAST day of group by result.