Export CSV

[Domoticatorino]

Hi,
set a user library with this following code:

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

then in a event script or scheduled script this following:

require('user.trend_pro')

-- Export data
name = 'trend name' -- trend name to export
dates = {}
dates['start'] = "month_ago"
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('mail_address@mail.com', 'Report from trend ' .. name, 'CSV file attached', attachement)