HomeLYnk - Timestamp issue for Energy Counter

[DK-Michael]

I have found a problem in the HomeLYnk when using the trend logger with counter values.

The data from "today" is marked as "yesterday" in the trends when viewing the data. I expect that this happens because the summations is made at midnight and therefore timestamped with the day after.

I have attached two pictures to show what I mean. As you can see on one picture, the total sum of Nov 18 is 3.2kWh but this is logged as Nov 19 on the "monthly" picture.

Does anyone have a solution to this?

//Michael

[Erwin-vd-Zwart · Accepted solution]

Hi Michael,

This is a know item and should be fixed in FW 1.4.

BR,

Erwin

[DK-Michael]

Hi Erwin

Thanks for the response. Is there an estimated time for the new firmware and will it fix the already logged values?

BR, Michael

[sesa189443]

Hi Michael

Some time in February and yes it will fix old trends. There will be new trend engine and old trends will be converted to new one during upgrade. We will also have lower sampling rate.

Regards

Daniel

[DK-Michael]

Hi Daniel

Thanks! That sounds great. Will we also get an easy way to export the data from the trends to some external system. E.g. if a customer have used the HomeLYnk/SpaceLYnk as a small energy server but wants to migrate to PME?

Kind regards, Michael

[sesa189443]

This you can do already by scripts but in new fw we will need new script as data will be stored in different place. In new FW there will be new possibility we integrated cloud connector and via cloud we can export data to 3rt party as csv. For now this will be available in France but other countries can join on request. 

[DK-Michael]

Hej Daniel

Can you share a template script for this with me?

Best regards, Michael

[Erwin-vd-Zwart]

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

[Erwin-vd-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

[DK-Michael]

Hi Erwin

That's awesome! Thanks a lot! I will try it later this week (:

BR, Michael