getHistory in REST API and in javascript adapters deliver "val = null" for boolean entries

theshengfui commented 6 months ago

Dear developers!

I am experiencing an issue with boolean typed states in a mariadb history. Number typed states seem to work as intended.

The values stored in the table seem correct: grafik

So I would expect to get 1710254347427 : true and 1710254349379 : false

when i ask for the history. The history in the admin panel is working as expected.

grafik

but when i use the REST API, I don't get those values:

I used the /getHistory Command with those parameters: { "id": "openknx.0.Kontakte.OG.Büro_Fenster_geöffnet", "options": { } }

Result:

[
  {
    "ts": 1710254347427,
    "val": null
  },
  {
    "ts": 1710254355012,
    "val": null
  }
]

Please also notice that one of the timestamps is not the one in the database. The wrong one seems to change every request. But I can't find it at all in the database.

In the javascript adapter i experience the same issue:

getHistory
(
    'sql.0',
    {
        id: "openknx.0.Kontakte.OG.Büro_Fenster_geöffnet"
    },
    (err, result) =>
    {
        if (err) console.error(err);
        if (result) 
        {
            for (let i = 0; i < result.length; i++) 
            {
               console.log(result[i]);
            }
        }
    }
);

produces:

16:14:39.352 | info | javascript.0 (422403) script.js.common.Debug: { ts: 1710254347427, val: null }
16:14:39.352 | info | javascript.0 (422403) script.js.common.Debug: { ts: 1710254354559, val: null }

Kind regards and thanks for looking into this!

theshengfui commented 6 months ago

I found the cause for this problem: when you don't set an "aggregate" value in options, it is set to the default value "average".

file main.js, function getHistory:

...
    const options = {
        id:         msg.message.id === '*' ? null : msg.message.id,
        start:      msg.message.options.start,
        end:        msg.message.options.end || (Date.now() + 5000000),
        step:       parseInt(msg.message.options.step, 10)  || null,
        count:      parseInt(msg.message.options.count, 10),
        ignoreNull: msg.message.options.ignoreNull,
        aggregate:  msg.message.options.aggregate || 'average', // One of: max, min, average, total, none, on-change

...

The issue is inside the aggregation to "average": it destroys the boolean values and sets them to null.

P.S.: I personally would set a non-delivered aggregate option to "none" but this is up to you, of course. I got my workaround for now. I will just add average: none to my requests.

Apollon77 commented 6 months ago

Thank you for the info! WIll speed up a fix hopefully. Changing that default fallback might be breaking, so I might not touch it but make sure it is documented in Readme

ioBroker / ioBroker.sql

getHistory in REST API and in javascript adapters deliver "val = null" for boolean entries #360