Reading trends from RRD in Lua

[buuuudzik]

Hello,

is there a possibility to send some commands to rrd database with trends for reading some specified data like it is with SQLLite db?

[Erwin van der Zwart]

Hi Buuuudzik,

See: http://www.openrb.com/docs/trends-new.htm

BR,

Erwin

[buuuudzik]

Thanks Erwin,

but I know about this API and I am using it, but it is very low-level and e.g. it hasn't timestamp only some values which are from start to end and also I've tested that fetch is not working properly when you try to  get the data from some range and also fetchone has some bugs or isn't accomplished like library from old-trends.

The other problem with current version of trend API which I've found is that when I want check only data from monday 12:00 to tuesday 7:00 I must download full monday and tuesday and doing whole calculation on not small table in Lua. And also when I want try something like this(fetching only a part of day):

Code:

1234567891011

require('trends') name = 'Słońce_Azymut' dates = {} dates['start'] = { year = 2017, month = 4, day = 6, hour=7} dates['end'] = { year = 2017, month = 4, day = 6, hour=12} -- resolution (in seconds) is optional -- default trend resolution is used when not set resolution = 3600 -- hourly data res, a, b = trends.fetch(name, dates, resolution) log(res)

The result is empty table A working way is when I download data from day=6 to day=7 and then I must do whole job in Lua.

http://forum.logicmachine.net/showthread...ght=trends

For me it would be perfect if I can analyse freely and reliably data from trends, so I can check freely every trend and find e.g. yearly maximum or how much time object crossed the limit and exactly when etc..

Perfect would be if this would be like in SQL because for me this is the best way for such analysis

I think also good option would be a tool for convert rrd db to SQL(but this I can do by converting whole data from Trend API, try to add to this data some timestamp and save it in .csv and then import such table to DB Browser)

[Erwin van der Zwart]

Hi Buuudzik,

Unfortunately that option is not available, but you can easily create simulair result including timestamp by using this:

Code:

123456789101112131415161718

require('trends') name = 'Słońce_Azymut' dates = {} dates['start'] = { year = 2017, month = 4, day = 6} dates['end'] = { year = 2017, month = 4, day = 7} -- default trend resolution is used when not set resolution = 3600 -- hourly data res, a, b = trends.fetch(name, dates, resolution) -- Create new table with timestamp and only values between 7 and 13 newtable = {} for i, row in ipairs(res) do  if (i-1) >= 7 and (i-1) <= 13 then      table.insert(newtable, {time = (i-1) .. ':00', value = row})  end end log(newtable)

If you resolution is 5 minutes you can convert it to hourly data like this:

Code:

1234567891011121314151617181920212223242526

require('trends') name = 'Słońce_Azymut' dates = {} dates['start'] = { year = 2017, month = 4, day = 6} dates['end'] = { year = 2017, month = 4, day = 7} -- default trend resolution is used when not set resolution = 3600 -- hourly data res, a, b = trends.fetch(name, dates, resolution) newtable = {} if #res == 288 then counter = 0 total = 0 for i, row in ipairs(res) do   total = total + row   counter = counter + 1   if counter == 12 then     if ((i/12)-1) >= 7 and ((i/12)-1) <= 13 then         table.insert(newtable, {time = ((i/12)-1) .. ':00', value = (total/12)})     end     counter = 0   end end end log(newtable)

If you resolution is 10 minutes change 288 to 144 and all 12 to 6 , for 15 minutes resolution change 288 to 96 and all 12 to 4, for 20 minutes resolution change 288 to 72 and all 12 to 3, for 30 minutes resolution change 288 to 48 and all 12 to 2.

BR,

Erwin

[buuuudzik]

Thank you Erwin,

I've prepared something also at this moment but now without converting to other output resolution.

This is a function for trend_export(can be added to user.library):

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

-- This function prepare export table and .csv file from defined range function trend_export(name, dates, mode, timestampType)     require('trends')   -- detect timeShift   function detectTimeShift(t0, t1)   t0_isdst, t1_isdst = os.date('*t', t0).isdst, os.date('*t', t1).isdst     if (t0_isdst ~= t1_isdst) then       if (t1_isdst) then         -- change to summertime         return 1       else         -- change to wintertime         return 2       end     else       return false     end   end   -- Convert word "now" in "start" to data table   if type(dates['start']) == "string" then     if dates['start'] == "now" then dates['start'] = os.date("*t", os.time())     elseif dates['start'] == "yesterday" then dates['start'] = os.date("*t", os.time()-24*3600)     elseif dates['start'] == "week_ago" then dates['start'] = os.date("*t", os.time()-7*24*3600)     elseif dates['start'] == "month_ago" then dates['start'] = os.date("*t", os.time()-30*24*3600)     elseif dates['start'] == "year_ago" then dates['start'] = os.date("*t", os.time()-365*24*3600)     end   end   -- Convert word "now" in "end" to data table   if type(dates['end']) == "string" then     if dates['end'] == "now" then dates['end'] = os.date("*t", os.time())     elseif dates['end'] == "yesterday" then dates['end'] = os.date("*t", os.time()-24*3600)     elseif dates['end'] == "week_ago" then dates['end'] = os.date("*t", os.time()-7*24*3600)     elseif dates['end'] == "month_ago" then dates['end'] = os.date("*t", os.time()-30*24*3600)     elseif dates['end'] == "year_ago" then dates['end'] = os.date("*t", os.time()-365*24*3600)     end   end             -- Start and end in unix time   starttime = os.time(dates['start'])   endtime = os.time(dates['end'])   -- Invert "start" and "end" when "end" is smaller than "start"   if starttime > endtime then     _ = dates['start']     dates['start'] = dates['end']     dates['end'] = _   end   -- Read trend resolution from DB   resolution = db:getlist("SELECT resolution*60 FROM trends WHERE name='" .. name .. "'")[1]   -- Prepare a table with the data which include the whole data from specified range   if resolution then       -- Expand a range with a one more day if there is some data in the last day     if (os.date("%H", endtime)*3600 + os.date("%M", endtime)*60) >= resolution then       dates['end'].day = dates['end'].day + 1       raw_data = trends.fetch(name, dates, resolution)     else       raw_data = trends.fetch(name, dates, resolution)     end     start = 1     data = {}     start_timestamp = os.time(dates['start']) + resolution     -- Cut the data and prepare a table with the data only from specified range     offset = 0     skip = 0     k = 1     while k <= #raw_data do       v = raw_data[k]              if k == 1 then last_timestamp = start_timestamp else last_timestamp = current_timestamp end       current_timestamp = start_timestamp + (k-1) * resolution + offset                    timeShiftStatus = detectTimeShift(last_timestamp, current_timestamp)       if timeShiftStatus then         if timeShiftStatus == 1 then -- winter->summer           skip = 6 -- skip 1 hour empty data           offset = offset - 3600         elseif timeShiftStatus == 2 then -- summer->winter           offset = offset + 3600         end       end              -- Add to new table only records from specified range       if current_timestamp >= starttime and current_timestamp <= endtime then         if timestampType == 'unixepoch' then data[start] = {current_timestamp, v}         else data[start] = {os.date('%Y.%m.%d %H:%M:%S', current_timestamp), v} end         start = start+1       end       k = k + 1 + skip       skip = 0     end          if mode == 'csv' then       -- Prepare .csv file       for k,v in ipairs(data) do               data[k] = table.concat(data[k], ",")       end         csv = "timestamp, value\r\n" .. table.concat(data, "\r\n")       return name, csv, resolution     elseif mode == 'table' then       return name, data, resolution     end   else       alert('There is no trend with such a name')   end   end

Function:
trend_export(name, dates, mode, timestampType)

Function parameters:
name - trend name(must be the same as in DB)
dates - a table with: year, month, day, hour or a string("now", "yesterday","week_ago", "month_ago", "year_ago")
mode - 'table' for lua table and 'csv' for e.g. sending mail and using it after in Excel
timestampType - 'unixepoch' for unix timestamp or false for using local human readable timestamp like '2017.03.26 07:20:00'

Function returns:
name - name of trends
data - csv or Lua table based on mode parameter
resolution - trend resolution in seconds e.g. 300 means 300/60 = 5 minutes

Example of using:

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445

require('user.trends_pro') -- Export data name = 'Kitchen_Temperature' dates = {} dates['start'] = { year=2016, month=4, day=5, hour=0} dates['end'] = "now" name, csv = trend_export(name, dates, 'csv', 'unixepoch') -- Send an email with data attachement = { {filename=name .. '.csv', filedata=csv, mimetype='text/csv'} } mailattach('someone@example.com', 'Report ' .. name, 'CSV file attached', attachement) -- Calculate maximum value in specified period and log it name, data = trend_export(name, dates, 'table') minValue, maxValue = -50, 100 currentMax = nil occurrences = {} for k,v in ipairs(data) do   curValue = v[2]     if not curMax then     curMax = curValue     table.insert(occurrences, v)   else     if curValue > minValue and curValue > curMax and curValue < maxValue then       curMax = curValue       occurrences = {}       table.insert(occurrences, v)     elseif curValue == currentMax then       table.insert(occurrences, v)     end   end end max = curMax if occurrences == 0 then log('There was no occurrences. Probably there was no data in table.') else     message = 'Maximum value of ' .. name .. ' equals ' .. max .. ' and there was ' .. #occurrences .. ' occurrence'   if #occurrences > 1 then message = message .. 's.' else message = message .. '.' end   log(message, occurrences) end

After export it is possible to import .csv file to e.g. program DB Browser for SQLite and then it is possible to use freely SQL commands to analyse this data for find e.g.:
- how many days in year was enough sunny(temp>20 and brightness>70klux) and in which month was the best for holiday?
- what would be the best angle for PV based on the last year?
- when there was no internet in house?
- how many times current temperature was very different from the setpoint and what was the reason?
- how many times in the last year there was some people in the house?
- ~~how many peoples was in the house in the las year daily based on doors(door open/2) or sensors(2 presence sensor in the hallway)?
and the other

[Domoticatorino]

Hi buuuudzik,
thank you for your reference. As per your post I proceed as follow:

- user library script
- scheduled script: you are referring to ('trends_pro').

But where do you declare 'trends_pro'?

[buuuudzik]

Hi buuuudzik,
thank you for your reference. As per your post I proceed as follow:

- user library script
- scheduled script: you are referring to ('trends_pro').

But where do you declare 'trends_pro'?

Sory, I've tested this library today in one script. Now I've updated post. You should use:

Code:

1

require('user.trends_pro')

[buuuudzik]

I've updated above library and now it can correct the data after timeshift and also it gives a possibility to select a type of timestamp: unixepoch or more human readable

[Domoticatorino]

Hi,
what mail user library do you suggest? Could you shown an example please? Thanks

[buuuudzik]

Here is a library which is on my LM in Common functions(but you can add it in new user library):
https://forum.logicmachine.net/showthrea...26#pid3726

In above post you have also an example of use

[Domoticatorino]

Hi, great job.

in you script you wrote:

name, csv = trend_export(name, dates, 'csv', 'unixepoch')

but I think you should complete as follow:

name, data, csv = trend_export(name, dates, 'csv', 'unixepoch').

In this way I received the file by mail. GREAT.

But unfortunatey time stamp is not human readable.

[buuuudzik]

Hi, great job.

in you script you wrote:

name, csv = trend_export(name, dates, 'csv', 'unixepoch')

but I think you should complete as follow:

name, data, csv = trend_export(name, dates, 'csv', 'unixepoch').

In this way I received the file by mail. GREAT.

But unfortunatey time stamp is not human readable.

I've updated above library a few times yesterday so maybe you has not last version and please check also last version of examples. Change return values was one of the last change. Now I'm thinking about add also some filter function which can be created by user e.g. check if value is from Monday and if then find min value from all Mondays from the specified period.

Yes, because you've specified 'unixepoch' Delete it and you will see human readable format.

[Domoticatorino]

Do you mean that I should write simply this below?

name, data, csv = trend_export(name, dates, 'csv')

But time stamp is going on to be unreadable.

[buuuudzik]

Please update library, This should work:

Code:

123456789101112131415

require('user.trends_pro') -- Export data name = 'Kitchen_Temperature' dates = {} dates['start'] = { year=2017, month=4, day=5, hour=0} dates['end'] = "now" name, csv = trend_export(name, dates, 'csv') -- Send an email with data attachement = { {filename=name .. '.csv', filedata=csv, mimetype='text/csv'} } mailattach('example@gmail.com', 'Report from trend ' .. name, 'CSV file attached', attachement) script.disable(_SCRIPTNAME)

The result is mail with csv which has such content:

[Domoticatorino]

Please update library, This should work:

Code:

123456789101112131415

require('user.trends_pro') -- Export data name = 'Kitchen_Temperature' dates = {} dates['start'] = { year=2017, month=4, day=5, hour=0} dates['end'] = "now" name, csv = trend_export(name, dates, 'csv') -- Send an email with data attachement = { {filename=name .. '.csv', filedata=csv, mimetype='text/csv'} } mailattach('example@gmail.com', 'Report from trend ' .. name, 'CSV file attached', attachement) script.disable(_SCRIPTNAME)

The result is mail with csv which has such content:

[buuuudzik]

Please paste here content of your user.trends_pro library and how you are using it. I will check.

[Domoticatorino]

Please paste here content of your user.trends_pro library and how you are using it. I will check.

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

-- This function prepare export table and .csv file from defined range function trend_export(name, dates, mode, timestampType)    require('trends')  -- detect timeShift  function detectTimeShift(t0, t1)  t0_isdst, t1_isdst = os.date('*t', t0).isdst, os.date('*t', t1).isdst    if (t0_isdst ~= t1_isdst) then      if (t1_isdst) then        -- change to summertime        return 1      else        -- change to wintertime        return 2      end    else      return false    end  end  -- Convert word "now" in "start" to data table  if type(dates['start']) == "string" then    if dates['start'] == "now" then dates['start'] = os.date("*t", os.time())    elseif dates['start'] == "yesterday" then dates['start'] = os.date("*t", os.time()-24*3600)    elseif dates['start'] == "week_ago" then dates['start'] = os.date("*t", os.time()-7*24*3600)    elseif dates['start'] == "month_ago" then dates['start'] = os.date("*t", os.time()-30*24*3600)    elseif dates['start'] == "year_ago" then dates['start'] = os.date("*t", os.time()-365*24*3600)    end  end  -- Convert word "now" in "end" to data table  if type(dates['end']) == "string" then    if dates['end'] == "now" then dates['end'] = os.date("*t", os.time())    elseif dates['end'] == "yesterday" then dates['end'] = os.date("*t", os.time()-24*3600)    elseif dates['end'] == "week_ago" then dates['end'] = os.date("*t", os.time()-7*24*3600)    elseif dates['end'] == "month_ago" then dates['end'] = os.date("*t", os.time()-30*24*3600)    elseif dates['end'] == "year_ago" then dates['end'] = os.date("*t", os.time()-365*24*3600)    end  end            -- Start and end in unix time  starttime = os.time(dates['start'])  endtime = os.time(dates['end'])  -- Invert "start" and "end" when "end" is smaller than "start"  if starttime > endtime then    _ = dates['start']    dates['start'] = dates['end']    dates['end'] = _  end  -- Read trend resolution from DB  resolution = db:getlist("SELECT resolution*60 FROM trends WHERE name='" .. name .. "'")[1]  -- Prepare a table with the data which include the whole data from specified range  if resolution then      -- Expand a range with a one more day if there is some data in the last day    if (os.date("%H", endtime)*3600 + os.date("%M", endtime)*60) >= resolution then      dates['end'].day = dates['end'].day + 1      raw_data = trends.fetch(name, dates, resolution)    else      raw_data = trends.fetch(name, dates, resolution)    end    start = 1    data = {}    start_timestamp = os.time(dates['start']) + resolution    -- Cut the data and prepare a table with the data only from specified range    offset = 0    skip = 0    k = 1    while k <= #raw_data do      v = raw_data[k]            if k == 1 then last_timestamp = start_timestamp else last_timestamp = current_timestamp end      current_timestamp = start_timestamp + (k-1) * resolution + offset                  timeShiftStatus = detectTimeShift(last_timestamp, current_timestamp)      if timeShiftStatus then        if timeShiftStatus == 1 then -- winter->summer          skip = 6 -- skip 1 hour empty data          offset = offset - 3600        elseif timeShiftStatus == 2 then -- summer->winter          offset = offset + 3600        end      end            -- Add to new table only records from specified range      if current_timestamp >= starttime and current_timestamp <= endtime then        if timestampType == 'unixepoch' then data[start] = {current_timestamp, v}        else data[start] = {os.date('%Y.%m.%d %H:%M:%S', current_timestamp), v} end        start = start+1      end      k = k + 1 + skip      skip = 0    end        if mode == 'csv' then      -- Prepare .csv file      for k,v in ipairs(data) do              data[k] = table.concat(data[k], ",")      end        csv = "timestamp, value\r\n" .. table.concat(data, "\r\n")      return name, csv    elseif mode == 'table' then      return name, data    end  else      alert('There is no trend with such a name')  end   end

[buuuudzik]

And how you are using it? Unfortunately demo LM from openrb.com is currently unavailable, so I cannot show you this on real LM.

Sometimes there are issues with reloading library so please clear library, clear and disable script and after all of these tasks try again use this library and script because it works for sure also with this human readable timestamp

[Domoticatorino]

GREAT! THANK YOU VERY MUCH!! You're right.

[buuuudzik]

Enjoy your evening