MySQL help

Add-ons Persistence Services
, ,
1

I have been playing with thansforms in persisting data and finally settled on a format but I don’t want to loose my old data how can I change the data in the column of rows devided by 1000 so that my charts are consistant.

here is the mysql call I am trying to use but it kicks an error

UPDATE item133 set Value = Value / 1000 SELECT FROM openhab.item133 WHERE Time BETWEEN '2016-02-18 12:34:01' AND '2016-02-20 07:19:00';

table item133

# Time, Value
2016-02-18 12:34:01, 13156.25
2016-02-18 12:34:31, 13156.25
2016-02-18 12:35:00, 13250
2016-02-18 12:35:30, 13250
2016-02-18 12:36:00, 13250
2016-02-18 12:36:30, 13250

i want the range to look like

# Time, Value
2016-02-18 12:34:01, 13.15625
2016-02-18 12:34:31, 13.15625
2016-02-18 12:35:00, 13.250
2016-02-18 12:35:30, 13.250
2016-02-18 12:36:00, 13.250
2016-02-18 12:36:30, 13.250

please help

2

Your best and safest way may be to

shutdown openhab
make a copy of the table
update the table based upon the copy
restart openhab

I won’t suggest sql statements because I can’t test what I post before I post it…

3

Because I was doing a history value I was able to do it while it was still running the code I neede to run was

UPDATE openhab.item133 set Value = Value / 1000  WHERE Time BETWEEN '2016-02-18 12:34:01' AND '2016-02-20 07:19:00'

removeing the

SELECT FROM openhab.item133

in the middle of the operation

now my graph looks normal