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:

-- 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:

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