HomeLYnk - Timestamp issue for Energy Counter

Hi Michael,

Use this for reading trends with script and export to CSV on internal FTP server: You need to enable the server first and check credentials and IP address in the scrip (default ftp:ftp)

require('socket.ftp')

require('genohm-scada.trends')

-- ftp file

ftpfile = string.format('ftp://ftp:ftp@192.168.10.200/' .. 'All trends last month data export' .. '_%s.csv', os.date('%Y-%m-%d_%H-%M', timestamp))

-- csv buffer

buffer = {}

-- Get all trend names from DB

trends_table = db:getall('SELECT name FROM trends ORDER BY name DESC')

for _, trend_names in ipairs(trends_table) do

  -- Get current timestamp

  timestamp = os.time()

  now = os.date('*t', timestamp)

  trend_name = trend_names.name

  -- Get last month for data and determine number of days in this month

  trenddatamonth = trends.fetch(trend_name, 'month', now)

  -- Add to buffer

  table.insert(buffer, '"Average usage a month (' .. trend_name .. ')"')

  -- Add empty line

  table.insert(buffer, '""')

  -- Add header

  table.insert(buffer, trend_name .. ',"Day","Average day value"')

  for _, row in ipairs(trenddatamonth) do

    -- format csv row

    csv = string.format('%q,%q,%q', "", "" .. string.format("%02d", row[1]) .. "-" .. string.format("%02d", now.month) .. "-" .. now.year, row[2])

    -- add to buffer

    table.insert(buffer, csv)

  end

  -- Add empty line

  table.insert(buffer, '""')

  table.insert(buffer, '"Average usage a day (' .. trend_name .. ')"')

  -- Add empty line

  table.insert(buffer, '""')

  for i = 1, now.day, 1 do

    now.day = i

    average_value = 0

    trenddata = trends.fetch(trend_name, 'day', now)

    -- Add header

    table.insert(buffer, trend_name .. ',' .. string.format("%02d", i) .. '-' .. string.format("%02d", now.month) .. '-' .. now.year .. ',"Average hour value"')

    for _, row in ipairs(trenddata) do

      -- format csv row

      csv = string.format('%q,%q,%q', "", "" .. string.format("%02d", (row[1]-1)) .. ":00:00", row[2])

      -- add to buffer

      table.insert(buffer, csv)

      average_value = average_value + row[2]

    end

    -- Add empty line

    table.insert(buffer, "")

    -- add to buffer

    table.insert(buffer, "Average value month (" .. string.format("%02d", now.month) .. "-" .. now.year .. ")," .. trenddatamonth[i][2])

    -- Add empty line

    table.insert(buffer, "")

  end

end

-- upload to ftp only when there's data in buffer

if #buffer > 1 then

  result, err = socket.ftp.put(ftpfile, table.concat(buffer, '\r\n'))

  -- error while uploading

  if err then

    alert('FTP upload error: %s', tostring(err))

  end

else

  log('No data found inside trend ' .. trend_name)

end

Good luck (:

BR,

Erwin van der Zwart

Hi Michael,

Use this script to read and export a single trend :

require('socket.ftp')

require('genohm-scada.trends')

trend_name = 'Your trend name to export'

-- Get current timestamp

timestamp = os.time()

now = os.date('*t', timestamp)

-- ftp file

ftpfile = string.format('ftp://ftp:ftp@192.168.10.200/' .. trend_name .. '_%s.csv', os.date('%Y-%m-%d_%H-%M', timestamp))

-- Get last month for data and determine number of days in this month

trenddatamonth = trends.fetch(trend_name, 'month', now)

log(trenddatamonth)

-- csv buffer

buffer = { '"Average usage a month"' }

-- Add empty line

table.insert(buffer, '""')

-- Add header

table.insert(buffer, trend_name .. ',"Day","Average day value"')

for _, row in ipairs(trenddatamonth) do

  -- format csv row

  csv = string.format('%q,%q,%q', "", "" .. string.format("%02d", row[1]) .. "-" .. string.format("%02d", now.month) .. "-" .. now.year, row[2])

  -- add to buffer

  table.insert(buffer, csv)

end

-- Add empty line

table.insert(buffer, '""')

table.insert(buffer, '"Average usage a day"')

-- Add empty line

table.insert(buffer, '""')

for i = 1, now.day, 1 do

  now.day = i

  average_value = 0

  trenddata = trends.fetch(trend_name, 'day', now)

  -- Add header

  table.insert(buffer, trend_name .. ',' .. string.format("%02d", i) .. '-' .. string.format("%02d", now.month) .. '-' .. now.year .. ',"Average hour value"')

  for _, row in ipairs(trenddata) do

  -- format csv row

  csv = string.format('%q,%q,%q', "", "" .. string.format("%02d", (row[1]-1)) .. ":00:00", row[2])

    -- add to buffer

    table.insert(buffer, csv)

    average_value = average_value + row[2]

  end

  -- Add empty line

  table.insert(buffer, "")

  -- add to buffer

  table.insert(buffer, "Average value month (" .. string.format("%02d", now.month) .. "-" .. now.year .. ")," .. trenddatamonth[i][2])

  -- Add empty line

  table.insert(buffer, "")

end

-- upload to ftp only when there's data in buffer

if #buffer > 1 then

  result, err = socket.ftp.put(ftpfile, table.concat(buffer, '\r\n'))

  -- error while uploading

  if err then

  alert('FTP upload error: %s', tostring(err))

  end

else

  log('No data found inside trend ' .. trend_name)

end

BR,

Erwin van der Zwart