Diagram Door - mySQL 0 and 1

(FyingEaglE) #1

hi,

why does the diagramm looks like this?

i’d expected a non linear diagram … because the data in mySQL DB is 0 or 1.

Chart item=door_sw03 service="mysql" period=1w refresh=300000

regards

(FyingEaglE) #2

Ok, i believe i have the fastest answer :smile:

in the moment of sending i had an idea … i defined it as string … as number it works like a charme.

… but …

why does the diagram looks all the time like this, even i change the period?

(Oliver Stenzel) #3

Hi,

i think it’s due to the way the standard chart item works.
It tries to scale the values somewhat reasonable. If your values are just 0 and 1, the result is what you see. For example, here’s how my chart for motion sensor would look:

To overcome this, I’m afraid you either have to use a more sophisticated chart engine (check this post: InfluxDB+Grafana persistence and graphing) or in the case of 0 1 values display the Open/Close times instead.
HTH,
Regards,
-OLI

(FyingEaglE) #4

i fully agree, but what i mean is the description of the horizontal axis.
it should give a chart binding …
you see my database, i think the displayed hours are not correct, and the chart is incorrect, too.

Chart item=door_sw03 service="mysql" period=30D refresh=300000

regards

(Oliver Stenzel) #5

Ah…OK, got your point.
What’s it looking like if you look at the graph from your browser?
Like:
http://YOUR-IP-HERE:8080/chart?items=door_sw03
If it’s better, I would then remove the “period=30D refresh=300000” part from your chart item and see what it looks like then and work from there.
If the browser view is messed up as well…maybe you need to truncate the data table for your item and gather new data sets. Kind of brute force, but best i can think of.

(FyingEaglE) #6

the db picture shows all available data.

the graph looks like the same, i left all additional parameters … no change
i do have other charts with more data, there i see a change by the period parameter.

(Oliver Stenzel) #7

Oh wait…the DB entries in your initial post are for item **str_**sw03 and in the chart item later on you reference item=**door_**sw03
Could it be that you just don’t have any datasets for item=**door_**sw03 ?

(FyingEaglE) #8

oh sorry, i shortened the name …

other chart with new data every minute:

4h

12h

24h

here is everything working as expected …

(Oliver Stenzel) #9

weird…but really, the only thing that imho sounds plausible is that you are directing the chart item to the wrong data set. Maybe double check the mapping in your db, which “ItemXX” is “door_sw03” mapped to in the “Items”-Table, then check the data in the table “Itemxx”

(FyingEaglE) #10

i checked it again and again and again … i think it’s correct …
i rechecked the database(s) and it is correct to …

(Oliver Stenzel) #11

Hmm…you wrote:

i defined it as string … as number it works like a charme.

so i suppose you changed your entry in the .items file?
I think this could provoke a second entry in the Items table with an identical name "door_sw03"
maybe you could check for this?

(FyingEaglE) #12

no, i renamed it for this thread … BUT … i changed it from string to number, and so i have to change from varchar to double … i am checking it right now.

(FyingEaglE) #13

now, nothing would be saved …

if i add some data manually, it will displayed correctly …

i hate it, nothing is working but no error messages … :triumph:

(Oliver Stenzel) #14

I would suggest:

  • delete DB entry (check for duplicates) for “door_sw03” in “Items” Table
  • Delete corresponding “ItemsXX” table (depending if any dups found, could be more than one)
  • trigger sensor to see if new entries created new entry in “Items” Table for “door_sw03” and if new “ItemsXX” table gets created and -hopefully - filled with data
(FyingEaglE) #15

by now i found out, that, if the last value is like the new value, it won’t be saved …
… strange but logical … but gives not good looking diagrams

(FyingEaglE) #16

is there a config parameter out there which says the persistence to save every collected value in my db?
the diagramm now looks like this, it would look nicer if i.e. zero-values are stored every time they came.

i got this values every minute, if the value changed, it would be saved.

(Oliver Stenzel) #17

I think the persistence only stores changes in a value automatically. if there is just a status update, this gets not saved. To achive saving of status updates, you will need to implement a rule; something like:

rule "Save update"
when Item door_sw03 received update
then
        postUpdate (door_sw03,door_sw03.state) 
end

You might need to fiddle with the parameters for postupdate, but this example should get you started.

(FyingEaglE) #18

i thought with this strategies could it be work, but , nope

// Persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies 
{
    everyMinute : "0 * * * * ?"
    everyHour     : "0 0 * * * ?"
    everyDay     : "0 0 0 * * ?"

    default = everyChange 
}

/*
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */

Items 
{
    grp_homebb01_persistence_mysql* : strategy = everyChange, everyHour, restoreOnStartup
    door_sw*  : strategy = everyChange, everyHour, restoreOnStartup, everyMinute
}

i will try your rule idea … i’ll report then …
by now i have other mysterious things to get work with other diagrams and periods …

(Oliver Stenzel) #19

i did a quick check here:

looks like my rule approach is unnecessary:

everyUpdate: persist the state whenever its state has been updated, even if it did not change

You will only need to add everyUpdate to your strategy for door_sw*

(FyingEaglE) #20

i think i have, if you look at my last post, but every minute do not seemed to work.

while i was writing this post, i refreshed the database to see last values

the persistence file was updated at

you can see, everyMinute is not working, and i do not have erros in openhab.log.

Additional Info

every item, that i want to persist, is member of this group, i added the item(s) door_sw* additional - there comes an idea to my mind: could it be, that door_sw* only works for groups and not for single items?

Persistence strategy