Problem in Querying the Data from influxdb


#1

Hello everyone.

I am currently interfacing ECG sensor with STM32F4 MCU. I am reading the serial data in batches (ex 1000 data points) and writing into influxdb. So in every second approx i have 70 data points stored.

Now when i use Grafana to plot the graph, i am unable to visualize the data points.

Reason can be because the timestamp (UTC) of my data points and the current time of Grafana differs as i am reading data points in batches and then writing it to database.

When i use Query Inspector, it give me “0” in Data tag.

Here is my Influxdb database with data points

2018-12-25T11:15:28.010445Z 1021
2018-12-25T11:15:28.024441Z 1026
2018-12-25T11:15:28.038441Z 1015
2018-12-25T11:15:28.052308Z 1016
2018-12-25T11:15:28.066444Z 1006
2018-12-25T11:15:28.080323Z 1044
2018-12-25T11:15:28.094432Z 1003
2018-12-25T11:15:28.108431Z 993
2018-12-25T11:15:28.122304Z 1014
2018-12-25T11:15:28.136433Z 1029
2018-12-25T11:15:28.150319Z 1022
2018-12-25T11:15:28.164431Z 1005
2018-12-25T11:15:28.178438Z 1006
2018-12-25T11:15:28.192321Z 1002
2018-12-25T11:15:28.206439Z 1033
2018-12-25T11:15:28.220323Z 1025
2018-12-25T11:15:28.234443Z 1018
2018-12-25T11:15:28.248441Z 1012
2018-12-25T11:15:28.262333Z 1005
2018-12-25T11:15:28.276441Z 1015
2018-12-25T11:15:28.290316Z 1032
2018-12-25T11:15:28.304426Z 1029
2018-12-25T11:15:28.31834Z 1028
2018-12-25T11:15:28.332321Z 1021
2018-12-25T11:15:28.346441Z 1035
2018-12-25T11:15:28.360305Z 1023
2018-12-25T11:15:28.374443Z 1036
2018-12-25T11:15:28.388324Z 1003
2018-12-25T11:15:28.402312Z 1020
2018-12-25T11:15:28.416435Z 1003
2018-12-25T11:15:28.430306Z 1014
2018-12-25T11:15:28.444435Z 1013
2018-12-25T11:15:28.458327Z 1008
2018-12-25T11:15:28.472322Z 993
2018-12-25T11:15:28.486444Z 1026
2018-12-25T11:15:28.500312Z 1003
2018-12-25T11:15:28.514452Z 1026
2018-12-25T11:15:28.528327Z 1018
2018-12-25T11:15:28.542323Z 1013
2018-12-25T11:15:28.556449Z 1007
2018-12-25T11:15:28.570341Z 1015
2018-12-25T11:15:28.584445Z 1032
2018-12-25T11:15:28.598318Z 1005
2018-12-25T11:15:28.612308Z 1037
2018-12-25T11:15:28.626438Z 1031
2018-12-25T11:15:28.640325Z 1017
2018-12-25T11:15:28.654437Z 1022
2018-12-25T11:15:28.668307Z 993
2018-12-25T11:15:28.682302Z 1018
2018-12-25T11:15:28.696434Z 1006
2018-12-25T11:15:28.710319Z 1012
2018-12-25T11:15:28.724435Z 1040
2018-12-25T11:15:28.738327Z 1007
2018-12-25T11:15:28.75235Z 1025
2018-12-25T11:15:28.766482Z 1024
2018-12-25T11:15:28.780337Z 1013
2018-12-25T11:15:28.794467Z 1028
2018-12-25T11:15:28.808319Z 1007
2018-12-25T11:15:28.822328Z 1028
2018-12-25T11:15:28.836332Z 1035
2018-12-25T11:15:28.850325Z 997
2018-12-25T11:15:28.864453Z 1022
2018-12-25T11:15:28.878342Z 1000
2018-12-25T11:15:28.892324Z 1024
2018-12-25T11:15:28.906316Z 1024
2018-12-25T11:15:28.920318Z 1005
2018-12-25T11:15:28.934434Z 1035
2018-12-25T11:15:28.948313Z 1009
2018-12-25T11:15:28.962322Z 1004
2018-12-25T11:15:28.97634Z 1016
2018-12-25T11:15:28.990325Z 1031
2018-12-25T11:15:29.004452Z 1028
2018-12-25T11:15:29.018355Z 1002
2018-12-25T11:15:29.03234Z 1033
2018-12-25T11:15:29.046339Z 1028
2018-12-25T11:15:29.060341Z 1005
2018-12-25T11:15:29.074472Z 1037

Here is my Metrics in Grafana

Can anyone suggest me how should i get the data plots from Grafana, whose time stamp differ in time with grafana. and Is there any other problem with my metrics ?


#2

I have also tried using time shift but it doesn’t help either.


#3

A couple of things:

The “current time of Grafana” shouldn’t matter, unless you’re saying that the clock on your server is actually set wrong. Grafana is timezone-aware and (depending on your dashboard setting) will either use local browser time or UTC. Simply make sure you set the Grafana time range to be something that includes the timestamps of the readings. You can set it to something like “last 24 hours” to be sure it’s large enough.

You may be better off starting off with a simpler query, e.g. without a distinct() selector (not sure why that’s there, but it might distort the timestamps), without a group-by-time, and without a fill(). E.g. you can go into manual query editor mode and start with a basic

SELECT rate FROM ECG

(or whatever you use in the Influx CLI to get the data dump above) to make sure you get the right data in the query inspector and in the chart, and then incrementally apply whatever WHERE clauses, GROUP BY clauses and selectors you need.


#4

On second thought, your timestamps may indeed be problematic. You made your post just after 06:00 UTC, yet your data has timestamps around 11:15 UTC on the same date. So it looks like you’re assigning timestamps way in the future, and indeed Grafana would not display that data. Make sure your timestamps are set correctly when you write to Influx. They MUST be UTC and not local time.


#5

Thanks.

Now I am able to read ECG sensor data from DB to Grafana.

But i wanted to know how to plot the original value from DB without using the parameter like mean,count etc.

Because when i plot the same DB data into excel sheet it gives me the interpretation of pulse, but i dont know what parameter should i use with FIND rate FROM ECG to plot it in Grafana properly.


#6

If you’re using the UI query editor you can simply remove the aggregation functions, and only leave field(ECG) in there. Also be sure to remove “group by time” otherwise you’ll get a query error.
http://docs.grafana.org/features/datasources/influxdb/#field-aggregation-functions

That should then return all the individual readings.

May be even easier to switch to raw query editor and make your changes there.


#7

As i remove “mean” from UI query editor, i don’t get output on Graph. And In query inspector,
the query shows " SELECT rate FROM ECG WHERE (“user” = “nikhil”)" AND time >= now() -24 hours GROUP BY time(2m) fill(none)"

And as you can see their is Group by time of 2m, but i haven’t set any group by time maually. From where to remove it ?


#8

If you’re using the UI editor you should be able to click on “time” under GROUP BY row and remove it.

"


#9

Hello, thanks for your help. I am now able to plot my ECG pulse.

I was thinking of counting my ECG pulse/sec. For that their are many multiple techniques, one technique is to see the number of Grid boxes between two pulses and divide it from 300.

But in my graph, I am not able to change the grid size. In old grafana edition their used to be Axes and Grid option. But now their is only setting related to Axis.

So can you help me, how to resize the grid ?