Again: wrong data in month-output; shifted 1 day in SBFspot.db

Topics: 4. Bugs
Sep 21, 2014 at 7:49 PM
When I check the data in SBFspot.db with: "SELECT * FROM MonthData ORDER BY TimeStamp DESC;" I noticed in the MonthData-table the DayYield data are one day ahead.
As an example I read at the 21st of September the energy generated (DayYield) of de 20th of September.
Please advice.
Sep 21, 2014 at 8:15 PM
Edited Sep 21, 2014 at 8:17 PM
dear,
please inform about the version of SBFspot, you're running,
please provide the debug output from sbfspot, use the options -d5 -v5 and apply 1>std-out.txt to write the information to a file

kr wim
Coordinator
Sep 21, 2014 at 8:15 PM
What Version of SBFspot?
Correct syntax is:
SELECT * FROM [vwMonthData];
or
select date(Dat.TimeStamp, 'unixepoch') AS TimeStamp,
    Inv.Name, Inv.Type, Dat.Serial,
    TotalYield, DayYield FROM MonthData Dat
INNER JOIN Inverters Inv ON Dat.Serial=Inv.Serial
ORDER BY Dat.Timestamp Desc;
Can you provide output of this query and Sunny explorer csv
thanks
Sep 22, 2014 at 9:40 AM
Edited Sep 22, 2014 at 11:52 AM
Dear Wim and SBF,

Thanks for your fast reactions!
I'm using SBFspot version 3.01.

The output of some queries are:
Image
If you want I can provide a link of the page above.

The results from sunny explorer:
sep=;
Version CSV1|Tool SE|Linebreaks CR/LF|Delimiter semicolon|Decimalpoint comma|Precision 3

;Kade13;Kade13
;SB 3000TL-21;SB 3000TL-21
;2130123367;2130123367
;Totale opbrengst;Dagopbrengst
;Counter;Analog
dd-MM-yyyy;kWh;kWh
01-09-2014;5371,385;10,532
...
17-09-2014;5561,036;15,411
18-09-2014;5576,053;15,017
19-09-2014;5590,544;14,491
20-09-2014;5601,771;11,227
21-09-2014;5611,890;10,119
You can also compare the results on: http://pvoutput.org/list.jsp?id=21070&sid=18953 and see the 1-day-shift. Pvoutput is updated by SBFspot.

The result when I run SBFspot with the -d5 - v5 options can be found on Dropbox

I run SBFspot with the options "-am0 -v2". At the beginning of the day "-am1 -v2" (I start my Pi every morning)
(I'm considering including option "-csv".)

Furthermore I included an extra table in SBFspot.db to monitor de Raspberry Pi. It has the records TimeStamp and Value.

Everything works fine, except the 1-day-shift...

Thanks for your answer.
/Frans
Coordinator
Sep 22, 2014 at 7:48 PM
Don't see a problem here... this is the decoded data from dropbox
60 04 1A 54 17/09/2014 22:00:00 UTC
CC DA 54 00 5561,036    15,411 kWh
00 00 00 00

E0 55 1B 54 18/09/2014 22:00:00 UTC
75 15 55 00 5576,053    15,017 kWh
00 00 00 00

60 A7 1C 54 19/09/2014 22:00:00 UTC
10 4E 55 00 5590,544    14,491 kWh
00 00 00 00

E0 F8 1D 54 20/09/2014 22:00:00 UTC
EB 79 55 00 5601,771    11,227 kWh
00 00 00 00

60 4A 1F 54 21/09/2014 22:00:00 UTC
72 A1 55 00 5611,890    10,119 kWh
00 00 00 00
Did you alter the vwMonthData view? (changed from 3.0.0 to 3.0.1)
You are converting the unix timestamp to local time (00:00:00) and it should be UTC (22:00:00) - This is causing the day shift
Marked as answer by SBF on 9/22/2014 at 12:48 PM
Sep 22, 2014 at 8:14 PM
No, I did not change it.
But I can see in my backupped databases that vwMonthData did not change with version 3.0.1.
In the 3.0.0 version there is an extra 'localtime' in what you posted earlier:
select date(Dat.TimeStamp, 'unixepoch', __'localtime'__) AS TimeStamp,
    Inv.Name, Inv.Type, Dat.Serial,
    TotalYield, DayYield FROM MonthData Dat
INNER JOIN Inverters Inv ON Dat.Serial=Inv.Serial
ORDER BY Dat.Timestamp Desc;
Should I try to remove it manually? I read somewhere of first deleting the view and than create a new one.
Coordinator
Sep 22, 2014 at 8:38 PM
Changing the view was part of the bugfix.
See Update_300_301_SQLite.sql in the source code and SBFspot 3.0.0 to 3.0.1 update procedure.pdf in the downloads section
Sep 22, 2014 at 9:38 PM
Thanks again SBF,

I changed the vwMonthData successfully. It looks nice now.
I do not know where the update to version 3.0.1 went wrong.

Hopefully it is now all OK.
Thanks for the advice's.

/Frans