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:

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:

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:

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:

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

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

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:

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:

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:

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