Hi,
I’m running Influxdb on a Pine64 (similar to a Raspberry Pi) which collects data from an Arduino which pushes measurement values to it. I view the values in Grafana.
One particular data type is raw values from a trim pot measuring the level in a water tank. The pot values varies in a sinusoidal way due to the way the float lowers with the water. This obviously provides an inaccurate reading especially as the tanks get low.
I’ve looked at the possibly of converting and re-displaying the values in Grafana, but it does not seem possible.
So my next option is to see if I can create another table (I have no idea of the inner workings of an Influx DB) that is a copy of my current water tank related measurements.
But when creating the copy is it possible to convert the values as part of the copy process? Specifically I’d need to run them through a Sin function.
Then I would just aim Grafana at the new db data and display that.
I hope that made sense, if it doesn’t please let me know.
Thank you.
Hi KISA ,
hope this example helps 
( remark : I noticed the sin seems wrong ? )
> create database sinus
> use sinus
Using database sinus
> insert base value=20
> insert base value=30
> insert base value=40
> insert base value=50
> select sin("value") into converted from base
name: result
time written
---- -------
0 4
> select * from converted
name: converted
time sin
---- ---
1554375107877518461 0.9129452507276277
1554375110020992181 -0.9880316240928618
1554375111828967735 0.7451131604793488
1554375114236889265 -0.26237485370392877
I see that the Sin is not correct ? I will check later …
Thanks for the reply.
I am completely new to influx DB and unfortunately I can’t make head or tail out of your explanation. Can you please explain what is going on and how I implement it?
Hi KISA , ( update : i figured it out that it has to do with radians and degrees )
the commands I showed are executed in influx , the command line interface to the influx database.
The first command you can ignore because the database already exists.
The insert commands create and inserts records ( datapoints)
in a table(measurement) named base with 1 column (field) named value.
Then I create a second table named converted using select sin(“value”) into …
The select at the end shows the content of my new table .
Then I used my calculator to check the calculated sines and I see that there is something not correct ,
my calculator says that sin(20) = 0.34 but I don’t find that value in my converted table.
So there must be something wrong in my example …
It’s been a while since I left school but the sine of 0 till 180 degrees should be between 0 and 1 no ?
> select value , sin("value") from base
name: base
time value sin
---- ----- ---
1554375107877518461 20 0.9129452507276277
1554375110020992181 30 -0.9880316240928618
1554375111828967735 40 0.7451131604793488
1554375114236889265 50 -0.26237485370392877
1554381151266200312 50 -0.26237485370392877
1554381687149800822 60 -0.3048106211022167
1554381689367353630 70 0.7738906815578891
1554381691572176133 80 -0.9938886539233751
1554381694278576097 90 0.893996663600558
update : it has to do with radians and degrees …
Thank again.
I have already worked out the equation I need to use for the conversion (it took me a while as well, since my schooling was long ago also :)).
Your example looks like I need to enter all the values manually to convert them. I need to convert a whole db table of values. The values have been collected every 5 minutes for about 6 months, so there are many values to convert.
Is there a way I can copy one whole database or table (I’m not sure which one is the correct term for this instance) to a new DB and have each value that is copied, converted my the sin equation before it is stored in the new DB?
Hi Kisa ,
good news , you don’t have to enter all the values manually ,
the inserts are to create some datapoints for my example.
Your datapoints are already in the database.
The conversion happens in
select sin("value") into converted from base
that command will read all datapoints from base , calculate the sin
on the field named value and store that in the measurement converted.
This all happens in the same database.
I don’t know if that will work for your 6 months of data ,
you may have to split it up using time windows ,
for example convert one month at a time or so …
You also have to copy the remaining fields if there are other fields .
Great, thanks for that. I’ll give it a go.
Ok, I’ve had a bit more of a look at what values I need to use.
For each value in my current table I need to run it though this equation:-
new_value = sin((measured_value - 49) * 0.163636) * 12,000) + 3000
I created a small table as you suggested, using values relevant for my requirement:-
InfluxDB shell version: 1.5.2
> create database sinus
> use sinus
Using database sinus
> insert base value=599
> insert base value=499
> insert base value=399
> insert base value=299
> insert base value=199
> insert base value=99
> insert base value=49
So I tried this first, which is the equation I need to get me the right values:-
select sin(("value" - 49) * 0.163636) * 12,000) + 3000 into converted from base
But when I try to run it I get:-
ERR: undefined function sin()
I also tried:-
select sin("value") into converted from base
But got the same error.
Any thoughts?
p.s. sin 0-90 = 0 -1
Hi Kisa,
you are using version 1.5.2 ,
But the sin() function was introduced
in version 1.6…
I did wonder if the version was the issue as I saw sin() as an option on the influxdb website. I’m currently trying to update my version of influxdb on the Pine64 board.
Is there any risk of me loseing data in the db by upgrading the version?
Looks like the upgrade worked:-
Connected to http://localhost:8086 version 1.5.2
InfluxDB shell version: 1.7.4
But it still gives the same error when using sin().
Not sure why is say’s"Connected to http://localhost:8086 version 1.5.2" when it’s version 1.7.4 though?
Hi ,
Modified after I saw your answer : have you restarted influxd ?
systemctl restart influxd
It is best to take a backup before upgrading , and to stop the database before upgrading
Influxd backup -portable /your-backup-location
systemctl stop influxd
Upgrade
systemctl start influxdThanks.
Backup and restart worked and now sin() is working.
Will try my conversion again 
