Trends to csv

[jobb@nordstrandsel.se]

Hello,

I want to create a csv file for the last 30 days.
I have a script as below, but it creates not that many days. What has to be changed?

/Fredrik

Code:

require('trends')

dates = {}
dates['start'] = os.date('*t')
dates['start'].day = dates['start'].day - 1
dates['end'] = os.date('*t')

now = os.time()

function fetch(tname)
  local res = {}
  local values = trends.fetch(tname, dates)
  local count = #values
  local step = 86400 / count

  for i, value in ipairs(values) do
    table.insert(res, {
      now - (count - i) * step,
      value
    })
  end

  return res
end

buf = {
  fetch('LUFTTEMPERATUR W'),
  fetch('NEDERBÖRDSMÄNGD W'),
  fetch('VINDRIKTNING W'),
  fetch('NEDERBÖRD INTENSITET W'),
  fetch('GENOMSNITTLIG VINDHASTIGHET W'),
  fetch('RELATIV LUFTFUKTIGHET W'),
  fetch('MAX VINDHASTIGHET W'),
  fetch('ABSOLUT LUFTTRYCK W')
}


csv = { 'Id,Site Id,Site Authentication Key,Report Date / Time,Temperature,Precipitation accumulated,Wind direction,Precipitation intensity,Average wind, Humidity,Downwind,Air pressure at station' }

for i, row1 in ipairs(buf[ 1 ]) do
  date = os.date('%d/%m/%y %H:%M', row1[ 1 ])
  value1 = row1[ 2 ]

  row2 = buf[ 2 ][ i ] or {}
  value2 = row2[ 2 ] or 0
 
  row3 = buf[ 3 ][ i ] or {}
  value3 = row3[ 2 ] or 0
 
  row4 = buf[ 4 ][ i ] or {}
  value4 = row4[ 2 ] or 0
 
  row5 = buf[ 5 ][ i ] or {}
  value5 = row5[ 2 ] or 0
 
  row6 = buf[ 6 ][ i ] or {}
  value6 = row6[ 2 ] or 0
 
  row7 = buf[ 7 ][ i ] or {}
  value7 = row7[ 2 ] or 0
 
  row8 = buf[ 8 ][ i ] or {}
  value8 = row8[ 2 ] or 0




 
    csv[ #csv + 1 ] = string.format(' fb6ca12c-3106-ee11-913a-201642ba599e,20230608tey3apeggae67rj4rymrfqtn3c,Grimeljer2023!,%s,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f',
    date, value1, value2, value3, value4, value5, value6, value7, value8)
 

end

csv = table.concat(csv, '\n')
log(csv)

to = 'email@email.com'

settings = {
  -- "from" field, only e-mail must be specified here
  from = ' email@email.com ',
  -- smtp username
  user = ' email@email.com ',
  -- smtp password
  password = 'Password',
  -- smtp server
  server = 'smtp.gmail.com',
  -- smtp server port
  port = 465,
  -- enable ssl, required for gmail smtp
  secure = 'sslv23',
}

subject = 'CSV väder'

smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')

function escape(v)
  return '<' .. tostring(v) .. '>'
end

to = escape(to)

msgt = {
  headers = {
    to = to,
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="logs.csv"',
    ['content-transfer-encoding'] = 'BASE64',
    subject = subject,
  },
  body = ltn12.source.chain(
    ltn12.source.string(csv),
    ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64'))
  )
}

settings.source = smtp.message(msgt)
settings.from = escape(settings.from)
settings.rcpt = { to }

res, err = smtp.send(settings)
log(res, err)

[admin]

Replace:

Code:

dates['start'].day = dates['start'].day - 1

With:

Code:

dates['start'].day = dates['start'].day - 30

[jobb@nordstrandsel.se]

Replace:

Code:

dates['start'].day = dates['start'].day - 1

With:

Code:

dates['start'].day = dates['start'].day - 30

Thank you!

But it seems to be only the last 24 hours in the csv file. What can be wrong?

/Fredrik

[admin]

Line 14 should be:

Code:

local step = 30 * 86400 / count

[jobb@nordstrandsel.se]

Thank you!

It seems to work, but the time and date does not match.

if i look at csv from script, date, time and values come like this.

" 23/10/06 14:30"  1010.56
" 23/10/06 14:45"  1011.20
" 23/10/06 15:00"  1011.20
" 23/10/06 15:15"  1011.20
" 23/10/06 15:30"  1011.20
" 23/10/06 15:45"  1011.20
" 23/10/06 16:00"  1011.20
" 23/10/06 16:15"  1011.20
" 23/10/06 16:30"  1011.20
" 23/10/06 16:45"  1011.20
" 23/10/06 17:00"  1011.20
" 23/10/06 17:15"  1011.20

And if i download a csv file from the day before 23/10/05 it shows this.

21:15,"1010.56"
21:30,"1011.2"
21:45,"1011.2"
22:00,"1011.2"
22:15,"1011.2"
22:30,"1011.2"
22:45,"1011.2"
23:00,"1011.2"
23:15,"1011.2"
23:30,"1011.2"
23:45,"1011.2"
24:00,"1011.2"

It means that the last value i was testing to send from script, is the last value from the day before.

How can i correct this?

/Fredrik

[admin]

Try replacing fetch function with this:

Code:

function fetch(tname)
  return trends.fetch(tname, dates, nil, true)
end

Make sure you have 2023 firmware.

[jobb@nordstrandsel.se]

Thank you!

Now it's 1h 45m difference.

if i look at csv from script, date, time and values come like this.

2023-10-08 22:45,7.90,0.00,291.20,0.00,2.40,52.00,2.80,1013.76"
2023-10-08 23:00,7.70,0.00,269.60,0.00,1.80,52.60,1.80,1013.12"
2023-10-08 23:15,7.50,0.00,287.36,0.00,2.30,52.88,2.40,1013.12"
2023-10-08 23:30,7.30,0.00,272.80,0.00,1.80,53.68,1.90,1013.12"
2023-10-08 23:45,7.20,0.00,302.72,0.00,1.10,55.00,1.10,1013.12"
2023-10-09 00:00,7.10,0.00,275.68,0.00,0.90,55.20,1.10,1013.12"
2023-10-09 00:15,7.00,0.00,299.20,0.00,1.40,55.00,2.50,1013.12"
2023-10-09 00:30,6.80,0.00,298.72,0.00,3.40,55.60,3.80,1013.12"
2023-10-09 00:45,6.60,0.00,291.36,0.00,3.40,56.28,3.60,1013.12"
2023-10-09 01:00,6.50,0.00,256.16,0.00,0.80,57.08,0.80,1013.12"
2023-10-09 01:15,6.40,0.00,296.64,0.00,1.40,58.80,1.50,1013.12"
2023-10-09 01:30,6.10,0.00,276.80,0.00,1.50,59.40,2.00,1013.12"
2023-10-09 01:45,6.00,0.00,350.40,0.00,0.50,59.68,0.60,1012.48"

And if i download a csv file from the day before 23/10/08 it shows this.

21:00,"1013.76"
21:15,"1013.12"
21:30,"1013.12"
21:45,"1013.12"
22:00,"1013.12"
22:15,"1013.12"
22:30,"1013.12"
22:45,"1013.12"
23:00,"1013.12"
23:15,"1013.12"
23:30,"1013.12"
23:45,"1013.12"
24:00,"1012.48"

How can i correct this?

/Fredrik

[admin]

Timestamps are in UTC which conversion to date string does not take into account.

Replace this:

Code:

date = os.date('%d/%m/%y %H:%M', row1[ 1 ])

With this:

Code:

date = os.date('!%d/%m/%y %H:%M', row1[ 1 ])

There's bug in CSV export in Trends UI where data is shifted by one step (15 minutes in your case).

[jobb@nordstrandsel.se]

Ok, thank you!

Now the result is this. It's 15 min difference...

Best!
/Fredrik

2023-10-09 20:45,7.70,0.00,251.84,0.00,1.00,63.00,1.10,1009.92"
2023-10-09 21:00,7.80,0.00,276.80,0.00,1.20,63.20,1.40,1009.28"
2023-10-09 21:15,7.50,0.00,268.16,0.00,1.30,65.00,1.50,1009.92"
2023-10-09 21:30,7.10,0.00,255.36,0.00,1.30,67.40,1.30,1009.92"
2023-10-09 21:45,7.10,0.00,240.48,0.00,0.50,67.40,0.60,1009.92"
2023-10-09 22:00,6.90,0.00,260.00,0.00,1.20,67.80,1.20,1009.92"
2023-10-09 22:15,6.60,0.00,263.04,0.00,0.60,71.60,0.80,1009.92"
2023-10-09 22:30,6.60,0.00,243.36,0.00,1.00,75.40,1.00,1009.92"
2023-10-09 22:45,6.60,0.00,239.52,0.00,1.20,76.40,1.40,1009.92"
2023-10-09 23:00,6.50,0.00,258.08,0.00,0.90,76.40,0.90,1009.92"
2023-10-09 23:15,6.30,0.00,197.12,0.00,0.60,76.60,0.60,1009.92"
2023-10-09 23:30,6.10,0.00,216.00,0.00,0.50,75.68,0.70,1009.92"
2023-10-09 23:45,5.90,0.00,199.68,0.00,1.70,75.20,2.10,1009.92"

20:45,"1009.28"
21:00,"1009.92"
21:15,"1009.28"
21:30,"1009.92"
21:45,"1009.92"
22:00,"1009.92"
22:15,"1009.92"
22:30,"1009.92"
22:45,"1009.92"
23:00,"1009.92"
23:15,"1009.92"
23:30,"1009.92"
23:45,"1009.92"
24:00,"1009.92"

[admin]

I've already mentioned this:
There's bug in CSV export in Trends UI where data is shifted by one step (15 minutes in your case).

Switch to Data when viewing trends, you will get the same 15 minutes difference with exported data.