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:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126

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:

1

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

With:

Code:

1

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

[jobb@nordstrandsel.se]

Replace:

Code:

1

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

With:

Code:

1

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:

1

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:

123

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:

1

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

With this:

Code:

1

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.