29.08.2018, 08:40
Hello everyone
is it possible to have a CSV file with all curves with this method?
Thanks.
BR
is it possible to have a CSV file with all curves with this method?
Thanks.
BR
|
Reading trends from RRD in Lua
|
|
29.08.2018, 08:49
For sure but you must change a script because there are 2 things:
1) how connect 2 trends with different resolution 2) be aware about the size of generated .csv (because of email limits and because of LM CPU power and memory) This is why I'm not created the script for doing all trends in one .csv. I think that better is create such script which generate all trends one by one with some pause between each email. And then you have all scripts and e.g. you can connect them via excel(if they are not too big) or via nodejs script or some windows program for such things.
Done is better than perfect
29.08.2018, 08:59
(29.08.2018, 08:49)buuuudzik Wrote: For sure but you must change a script because there are 2 things: Indeed the CPU can be used too much. After making a backup there is no possibility to recover all of this data in a file? I would like to retrieve all the data on 65 curves (same resolution) for 6 months ... it's really a lot by mail Thank you for Reply. BR
I've updated a trends_pro library to allow simple modifying its data and adding more calculated column.
Code: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193 -- This function prepare csv table content from data and passed headers
function generateCSVTable(rows, headers)
if #rows > 0 then
if #rows[1] > #headers then
error("There should be " .. tostring(#rows[1]) .. " header columns!")
end
end
-- Prepare .csv file
for k,v in ipairs(rows) do
rows[k] = table.concat(v, ",")
end
csv = table.concat(headers, ",") .. "\r\n" .. table.concat(rows, "\r\n")
return csv
end
-- Allows to add columns based on a current row or a previous one to current row
function addColumnsToRow(row, prevRow, columnGetters)
for k,getter in ipairs(columnGetters) do
table.insert(row, getter(row, prevRow))
end
return row
end
-- Allows to add columns based on a current row or a previous one to every row
function addColumnsToRows(rows, columnGetters)
local prev = nil
for k,row in ipairs(rows) do
rows[k] = addColumnsToRow(row, prevRow, columnGetters)
prev = row
end
return rows
end
function copyObject(obj)
return json.decode(json.encode(obj))
end
-- 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
require("json")
dates = copyObject(dates)
-- 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
csv = generateCSVTable(data, { "timestamp", "value" })
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
-- Merge values from multiple trends in one table
function mergeRows(baseRows, rows)
for k,row in ipairs(baseRows) do
if k < 3 then log(k, "base", row) end
table.insert(baseRows[k], rows[k][2])
if k < 3 then log(k, "next", rows[k]) end
end
return baseRows
end
function getMultipleColumnHeader(names)
local columns = { "timestamp" }
for k,name in ipairs(names) do
table.insert(columns, name)
end
return columns
end
-- This function prepare export table and .csv file from defined range
function multiple_trend_export(names, dates, mode, timestampType)
local baseRows = {}
for k,name in ipairs(names) do
local trend_name, rows = trend_export(name, dates, 'table', timestampType)
if #baseRows == 0 then
baseRows = rows
else
baseRows = mergeRows(baseRows, rows)
end
end
if mode == 'csv' then
-- Prepare .csv file
csv = generateCSVTable(baseRows, getMultipleColumnHeader(names))
return names, csv, resolution
elseif mode == 'table' then
return names, baseRows, resolution
end
endWhat's changed: I've added these function: addColumnsToRows(rows, columnGetters) - which will allow to add more columns and calculate values based on current row and a previous one generateCSVTable(rows, headers) - which will produce a csv table content with named headers which should have same length as data rows multiple_trend_export(names, dates, mode, timestampType) - which can be used for generating a table with multiple trends Usage example: Code: 12345678910111213141516171819202122232425262728293031323334 require('user.trends_pro')
dates = {}
dates['start'] = 'yesterday'
dates['end'] = 'now'
name, rows = trend_export("Salon_Temperatura", dates, 'table', 'unixepoch')
addColumnsToRows(rows, {
function(row, prevRow)
local value = row[2]
local prevValue = 0
if prevRow then prevValue = prevRow[2] end
if value > 3 or prevValue > 3 then
return "YES"
else
return "NO"
end
end
})
csv = generateCSVTable(rows, { 'timestamp', 'value', 'isBiggerThan3' })
log(name, csv)
-- Generating a table with multiple trends in same rows
names, rows = multiple_trend_export({"Kitchen_Temp", "Outdoor_Temp"}, dates, 'csv', 'unixepoch')
log(names, rows)
Done is better than perfect
07.07.2022, 17:09
Hi!
Thanks for your effort with this Lib ! I is very useful ! I have tried it and noticed a small difference from the Export from Visualization and the Export from the Script. The resolution for this trends I have tested are 30min. There is an offset of 30 minutes in the timestamp: Code: 12345 -- This the timewindow I have used to test
dates = {}
dates['start'] = { year = 2022, month = 6, day = 1, hour = 0, min = 0, sec = 0}
dates['end'] = { year = 2022, month = 7, day = 1, hour = 0, min = 0, sec = 0} |
|
« Next Oldest | Next Newest »
|