Reading trends from RRD in Lua

[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