Wrong query leads to no data uploaded to PVOutputs

[GoogleCodeExporter]

For an unknown reason my inverter stores the data values not at a rount 
interval as 13:05:00 but at something like 13:07:42. For that reason the query

"SELECT DATE_FORMAT(dd1.DateTime,\'%%Y%%m%%d\'), 
DATE_FORMAT(dd1.DateTime,\'%%H:%%i\'), 
ROUND((dd1.ETotalToday-dd2.EtotalToday)*1000), dd1.CurrentPower, dd1.DateTime 
FROM DayData as dd1 join DayData as dd2 on 
dd2.DateTime=DATE_FORMAT(dd1.DateTime,\'%%Y%%m%%d0000000\') WHERE 
dd1.DateTime>=Date_Sub(CURDATE(),INTERVAL 1 DAY) and dd1.PVOutput IS NULL and 
dd1.CurrentPower>0 ORDER BY dd1.DateTime ASC"

does not return any hit.

To fix it I had to change both the date format and condition of the join clause 
as following:

"SELECT DATE_FORMAT(dd1.DateTime,\'%%Y%%m%%d\'), 
DATE_FORMAT(dd1.DateTime,\'%%H:%%i\'), 
ROUND((dd1.ETotalToday-dd2.EtotalToday)*1000), dd1.CurrentPower, dd1.DateTime 
FROM DayData as dd1 join DayData as dd2 on 
dd2.DateTime>=DATE_FORMAT(dd1.DateTime,\'%%Y-%%m-%%d 00:00:00\') and 
dd2.DateTime<=DATE_FORMAT(dd1.DateTime,\'%%Y-%%m-%%d 00:05:00\') WHERE 
dd1.DateTime>=Date_Sub(CURDATE(),INTERVAL 1 DAY) and dd1.PVOutput IS NULL and 
dd1.CurrentPower>0 ORDER BY dd1.DateTime ASC"

I've done that for all the queries containing "%%Y%%m%%d0000000" and now it 
works like a charm.

Thanks to the team for this great project!

Wally

Original issue reported on code.google.com by wa...@cattopasto.com on 17 Jul 2011 at 7:27

[GoogleCodeExporter]

Thanks for reporting this. Had the same problem on my SMA-5000TL-20

Original comment by marc.ram...@gmail.com on 3 Dec 2011 at 2:47

[GoogleCodeExporter]

Hello,

I can't compile the .c file after changing the two last querry lines (3 at all)
I'm getting the following error:

smatool.c:2823: error: expected identifier or '(' before '}' token
smatool.c:2824: warning: data definition has no type or storage class
smatool.c:2824: warning: parameter names (without types) in function declaration
smatool.c:2825: error: expected identifier or '(' before '}' token
smatool.c:2826: warning: data definition has no type or storage class
smatool.c:2826: warning: parameter names (without types) in function declaration
smatool.c:2826: error: conflicting types for 'mysql_close'
/usr/include/mysql/mysql.h:830: error: previous declaration of 'mysql_close' 
was here
smatool.c:2827: error: expected identifier or '(' before '}' token
smatool.c:2829: error: expected identifier or '(' before 'return'
smatool.c:2830: error: expected identifier or '(' before '}' token
make: *** [smatool.o] Error 1

Original comment by christop...@googlemail.com on 27 Apr 2012 at 8:54

[GoogleCodeExporter]

Should this script be retrieving data to store in MySQL even when there is no 
data (ie inverters switched off)?  

My inverter seems to only report data when energy is being generated, as a 
result, the JOIN clause using 00:00:00 will always give no rows because there 
is no entry for the time of 00:00:00 with it being midnight, and hence no data 
sent to pvoutput.org.

Original comment by cj.au...@gmail.com on 2 Aug 2012 at 1:56

[GoogleCodeExporter]

Verified that was the problem in my case (no entries with timestamp 00:00:00) 
by manually adding one and got some pvoutput data to appear.

With the help of a friend, we have a query that should yield the correct result 
- not yet tested.  Swapping the JOIN part by replacing:

join DayData as dd2 on 
dd2.DateTime=DATE_FORMAT(dd1.DateTime,\'%%Y%%m%%d0000000\')

With

JOIN (SELECT DATE_FORMAT( DateTime, \'%%Y-%%m-%%d\' ) AS DateTime, MIN( 
ETotalToday ) AS ETotalToday FROM DayData GROUP BY DATE_FORMAT( DateTime, 
\'%%Y-%%m-%%d\' )) AS dd2 ON dd2.DateTime = DATE_FORMAT( dd1.DateTime, 
\'%%Y-%%m-%%d\' )

Running the query with that manually generates expected results, yet to try 
editing the .c file to make the change, but I'd expect it to also work.

Original comment by cj.au...@gmail.com on 2 Aug 2012 at 5:34

[GoogleCodeExporter]

WOW just install smatool and am trying to get it to upload to PVOutput without 
success. Found this forum and it looks pretty obvious that smatool is only 
designed to work with inverters that store data every 5 minutes 24 hours a day. 
I have the SB2500 which I got 3 years ago and using Sunny Explorer shows that 
it only stores data when the panels have got the minimum amount of power to 
turn on the inverter. So the first entry is at about 06:00 and the last at 
about 20:30 during summer. The start time will be later and finish time earlier 
during winter.

I had a look at smatool.c and counted at least 5 places that rely on either a 
entry at 00:00:00 or 23:55:00.

Has anyone made all the changes to the code to allow for the inverters like 
mine and got it working successfully? 

I also have the same problem that I saw mentioned somewhere else about 
sporadically have rows in the mysql database with CurrentPower =  -2147483648 
and ETotalToday = 9999999.999. This is definetly a bug in smatool.c because 
Sunny Explorer has no problems when I export the data to a csv file. Has anyone 
solved that problem as well?

Original comment by brunofa...@gmail.com on 13 Dec 2012 at 12:48

[GoogleCodeExporter]

One relatively simple work around to this is to run a mysql script every 
morning using cron at say 01:00 which gets the last EtotalToday value and adds 
a row with this value for the previous day at 23:55:00 and the current day at 
00:00:00. I'll try that and see what happens.

Original comment by brunofa...@gmail.com on 13 Dec 2012 at 1:46