No data in PvoData, DayData, MonthData etc

Topics: 3. Feature Requests, 4. Bugs, 5. Support
Dec 22, 2015 at 10:02 AM
Edited Dec 27, 2015 at 6:09 PM
Im using V3.1.2 with sqlite3 on a Raspberry Pi connected by bluetooth to a SMA Sunny Boy 5000TL-20.

Connection and collecting data to table SpotData respectively view vwSpotData each 5 minutes works fine. csv-export works as well.

But there is no data in the other tables (like PvoData, DayData, MonthData etc), so it does not any upload to PVOutput. I tried MySQL with the same negative result.

Do I have to configure something in the inverter itself? Could somebody help please?

For analysis I run sbfspot with parameters -d5 -v5 -finq -ad3 -am2 1>heilu-out-log.txt 2>heilu-err-log.txt

No text in error-Log.

Start of Output-log (as I do not know how to attach files to this thread):
SBFspot V3.1.2
Yet another tool to read power production of SMA solar inverters
(c) 2012-2015, SBF (https://sbfspot.codeplex.com)
Compiled for Linux (LE) 32 bit

Commandline Args: -d5 -v5 -finq -ad3 -am2
Reading config '/usr/local/bin/sbfspot.3/SBFspot.cfg'
Configuration settings:
BTAddress=00:80:25:1F:0C:8E
Password=<undisclosed>
MIS_Enabled=0
Plantname=<undisclosed>
OutputPath=/home/pi/smadata/%Y
OutputPathEvents=/home/pi/smadata/%Y/Events
Latitude=50.16
Longitude=9.35
Timezone=Europe/Berlin
CalculateMissingSpotValues=1
DateTimeFormat=%d/%m/%Y %H:%M:%S
DateFormat=%d/%m/%Y
TimeFormat=%H:%M:%S
SynchTime=1
SunRSOffset=900
DecimalPoint=comma
CSV_Delimiter=semicolon
Precision=3
CSV_Export=1
CSV_ExtendedHeader=1
CSV_Header=1
CSV_SaveZeroPower=1
CSV_Spot_TimeSource=0
CSV_Spot_WebboxHeader=0
Locale=de-DE
BTConnectRetries=10
SQL_Database=/home/pi/smadata/SBFspot.db
### End of Config ###
Tue Dec 22 12:06:31 2015: INFO: Starting...
sunrise: 08:20
sunset : 16:21
Connecting to 00:80:25:1F:0C:8E (1/10)
Initializing...
SUSyID: 125 - SessionID: 846885662 (0x327A731E)
getPacket(2)
MAX_CommBuf is now 18 bytes
Received 18 bytes
Received 13 bytes
--------: 00 01 02 03 04 05 06 07 08 09
00000000: 7E 1F 00 61 8E 0C 1F 25 80 00 
00000010: 00 00 00 00 00 00 02 00 00 04 
00000020: 70 00 01 00 00 00 00 01 00 00 
00000030: 00 
cmd=2
<<<====== Content of pcktBuf =======>>>
--------: 00 01 02 03 04 05 06 07 08 09
00000000: 7E 1F 00 61 8E 0C 1F 25 80 00 
00000010: 00 00 00 00 00 00 02 00 00 04 
00000020: 70 00 01 00 00 00 00 01 00 00 
00000030: 00 
<<<=================================>>>
MAX_pcktBuf is now 31 bytes
SMA netID=01

<more lines available>
Dec 22, 2015 at 11:18 AM
Tried V3.0.3 with the same negative result.
Dec 22, 2015 at 11:53 AM
dear "jofland",

-a- after reading your description can you be more specific about the below marked points:

-a.1- Connection and collecting data to table SpotData respectively view vwSpotData each 5 minutes works fine. csv-export works as well. ????
!!! since the functionality of sbfspot has two distinct steps:
  • sbfspot interacts with the inverter and creates a database (and/or csv files)
  • uploaddaemon extracts the data from the database and sends that data to PVoutput
Question: can you confirm that SBFspot generates a csv output for each of the tables: spot day month (filenames that look like: <<Plantname>>yymmdd.csv)

-a.2- But there is no data in the other tables (like PvoData, DayData, MonthData etc), ????


-a.3- so it does not any upload to PVOutput. I tried MySQL with the same negative result.

Question: can you provide the config information for the uploaddaemon


-b- the discussion forum does not allow to attach files, the alternate way is to send info via private email,
the "captured text" above does not give sufficient information

note: - when you apply the instructions, as you did to catch the detailed output from sbfspot,
please modify the indication "heilu" by your username "jofland" and a dateinfo

example: -d5 -v5 -finq -ad3 -am2 1>jofl-dec22-out-log.txt 2>jofl-dec22-err-log.txtsince the

!!! your user profile in codeplex does not allow that i can send a private message to you,
please contact me by clicking on my username (in blue in the first column)

kind regards wim
Coordinator
Dec 22, 2015 at 12:12 PM
Edited Dec 22, 2015 at 12:12 PM
Did you check the time of your inverter? It should be in the logfile.
The SynchTime=1 option in the config should allow SBFspot to set the correct time, but we noticed this doesn't work allways.
If needed, you can set the time with SunnyExplorer (windows only)
Dec 22, 2015 at 3:57 PM
@SBF

Thank your for your hint.

It seems that the time in the converter is not correct:
Current Inverter Time: 27/03/2012 05:34:02

I will try to correct the time with SunnyExplorer, but unfortunately not before the next days (no Windows-PC available...).

How is the converter-time linked with the processes of SBFspot? How does it work? What are the consequences of a wrong converter-time?
Dec 22, 2015 at 4:12 PM
@wim

Thank you for your quick help. I will send you my config and log files via pm.

Regarding to your questions:

-a.1: I know that SBFspot and SBFspotUpload are different steps and different programs. As fare as I understood the code, I think the uploaddaemon reads the data out of the database (pvo-table) which sbfspot put in.

What I did not understand is, how often a day the data in the DayData-table is updated (only once or at each call of sbfspot)?
What I did not understand either is, when will the csv-files be written?

The files written are:
pi@loggerpi:~ $ sudo /usr/local/bin/sbfspot.3/SBFspot -d5 -v5 -finq -ad3 -am2 1>jofland-20151222-out-log.txt 2>jofland-20151222-err-log.txt
pi@loggerpi:~ $ date
Di 22. Dez 17:38:10 CET 2015
pi@loggerpi:~ $ ls -l smadata/2012
insgesamt 336
<deleted some lines>
-rw-r--r-- 1 pi pi 26109 Dez 19 13:15 Bieber_WZ-Spot-20120323.csv
-rw-r--r-- 1 pi pi 26438 Dez 20 13:15 Bieber_WZ-Spot-20120324.csv
-rw-r--r-- 1 pi pi 29219 Dez 21 12:15 Bieber_WZ-Spot-20120325.csv
-rw-r--r-- 1 pi pi 26324 Dez 22 12:15 Bieber_WZ-Spot-20120326.csv
-rw-r--r-- 1 pi pi 13909 Dez 22 17:38 Bieber_WZ-Spot-20120327.csv
pi@loggerpi:~ $ ls -l smadata/2015
insgesamt 4
drwxr-xr-x 2 pi pi 4096 Dez 13 20:31 Events
pi@loggerpi:~ $ ls -l smadata/2015/Events/
insgesamt 12
-rw-r--r-- 1 pi pi 4206 Dez 13 20:34 Bieber_WZ-User-Events-201201-201201.csv
-rw-r--r-- 1 pi pi  207 Dez 22 17:38 Bieber_WZ-User-Events-201512-201512.csv
So it is only spot and event files. AND the spot files have timestamp 2012; maybe because of the wrong converter-time.

-a.2: That is in my database:
sqlite> select * from vwSpotData limit 10;
2015-12-22 17:43:19|2015-12-22 17:45:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|0.0|0.0|0|0|0|0.0|0.0|0.0|0.0|0.0|0.0|0|0|0|3148|23132656|0.0|17689.7|17055.4|80.0|OK|N/A|0.0
2015-12-22 17:42:12|2015-12-22 17:40:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|0.0|0.0|0|0|0|0.0|0.0|0.0|0.0|0.0|0.0|0|0|0|3148|23132656|0.0|17689.7|17055.4|80.0|OK|N/A|0.0
2015-12-22 17:42:03|2015-12-22 17:40:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|0.0|0.0|0|0|0|0.0|0.0|0.0|0.0|0.0|0.0|0|0|0|3148|23132656|0.0|17689.7|17055.4|80.0|OK|N/A|0.0
2015-12-22 17:38:06|2015-12-22 17:40:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|0.0|0.0|0|0|0|0.0|0.0|0.0|0.0|0.0|0.0|0|0|0|3148|23132656|0.0|17689.7|17055.4|80.4|OK|N/A|0.0
2015-12-22 16:35:04|2015-12-22 16:35:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|0.0|0.0|0|0|0|0.0|0.0|0.0|0.0|0.0|0.0|0|0|0|3148|23132656|0.0|17689.7|17055.4|80.0|OK|N/A|0.0
2015-12-22 16:30:04|2015-12-22 16:30:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|0.0|0.0|0|0|0|0.0|0.0|0.0|0.0|0.0|0.0|0|0|0|3148|23132656|0.0|17689.7|17055.4|80.0|OK|N/A|0.0
2015-12-22 16:25:04|2015-12-22 16:25:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|233.68|233.59|0|0|0|0.0|0.0|0.0|230.38|0.0|0.0|0|0|0|3148|23132656|50.0|17689.7|17055.4|80.0|OK|Open|46.8
2015-12-22 16:20:04|2015-12-22 16:20:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|283.5|283.45|0|0|0|0.0|0.0|0.0|230.01|0.0|0.0|0|0|0|3148|23132656|50.02|17689.6|17055.4|80.0|OK|Open|47.0
2015-12-22 16:15:03|2015-12-22 16:15:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|309.31|309.48|0|0|0|0.0|0.0|0.0|230.05|0.0|0.0|0|0|0|3148|23132656|50.02|17689.5|17055.4|80.0|OK|Open|47.4
2015-12-22 16:10:05|2015-12-22 16:10:00|SN: 2100499932|SB 5000TL-20|2100499932|0|0|0.0|0.0|310.2|310.15|0|0|0|0.0|0.0|0.0|228.56|0.0|0.0|0|0|0|3148|23132656|50.01|17689.5|17055.4|80.0|OK|Open|48.1
sqlite> select * from vwDayData;
sqlite> select * from vwPvoData;
sqlite> 
-a.3: I think it does no upload because of the missing data in pvo and day-tables. Here is my upload config:
LogDir=/home/pi/smadata/logs
PVoutput_SID=2100499932:42433
PVoutput_Key=<removed>
SQL_Database=/home/pi/smadata/SBFspot.db
Dec 23, 2015 at 1:21 PM
Thanks,

after setting the converter-time with SunnyExplorer I had some data in the day and month table.

In the month table the time between 2015-12 and 2012-03 is missing. I consider the 2012 data is from 2015-11 and below. Is there any chance to get the correct values? Do I have a simple update in the datetime columns? How to calculate the offset?
Dec 23, 2015 at 8:13 PM
dear jofland,

i do start with your last point
""Is there any chance to get the correct values? Do I have a simple update in the datetime columns? How to calculate the offset?""
  • to retrieve the maximum of information execute sbfspot with the following options (one step at a time)
    preparation: -> modify the config file sucht that the outputs will be written to a different directory
    step1 - get monthdata: sbfspot -am110 -nosql -finq -ad0 -ae0 -sp0 1>jofmon-out1.txt that will extract ALL month information in csv format
    step2 - get dailydata: sbfspot -ad62 -nosql -finq -am0 -ae0 -sp0 1>jofday-out2.txt that will extract the daily files
    step3 - get olddailydata: sbfspot -startdate:20120329 -ad80 -nosql -finq -am0 -ae0 -sp0 1>jofold-out3.txt that might extract the "old" daily files
step4 - get events: sbfspot -ae110 -nosql -finq -ad0 -am0 -spo 1>jofeve-out4.txt
step5 - get events: sbfspot -startdate:20120506 -ae60 -nosql -finq -ad0 -am0 -spo 1>jofeve-out5.txt

!w! we can not predict - what exactly was done by your inverter at the moment that the date-time was modified
  • from experience the month data is not lost (timestamps might be modified according the new time)
??? please send as well - the two event files that you did already have available
      • below is more about the discussion - - -
-a- as indicated by sbf it is an absolute need to set the "Date-Time" of your inverter to the correct value,
  • in earlier versions of sbfspot the - Inverter's -Date-Time was correctly adjusted to actual time,
    in the latest versions of sbfspot that function does not work correctly, :-) is under investigation
  • for that reason one needs to adjust, at least once, the inverters clock to the "ACTUAL" == local Date-Time
    to adjust the time one can use - either: - sunny explorer or: - devices SunnyBeam, . . .
-b- you have "rediscovered" a common issue related to SMA inverters
for a large number of installations (avg >50%) the "Date-Time" of the inverter is not set to the "ACTUAL" date time
as part of the installation,
  • for that reason, after installation, the "clock" of that SMA-inverter will start from the "default-value",
  • the default value is very often 2008jan01 00h00m00s - (depends on type of inverter)
  • that explains your Current Inverter Time: 27/03/2012 05:34:02 <- cfr your info
  • the below info might give you an indication about the way that sbfspot handles date-time settings
-c- notice about sbfspot functions for the spot values
when sbfspot is executed (with csv enabled) -> then a "spot" csv table is created "yoursystem" &spot& "inverter-date" .csv
see your comment -> below an extract from your info:
-rw-r--r-- 1 pi pi 26109 Dez 19 13:15 Bieber_WZ-Spot-20120323.csv
-rw-r--r-- 1 pi pi 26438 Dez 20 13:15 Bieber_WZ-Spot-20120324.csv
-rw-r--r-- 1 pi pi 29219 Dez 21 12:15 Bieber_WZ-Spot-20120325.csv
-rw-r--r-- 1 pi pi 26324 Dez 22 12:15 Bieber_WZ-Spot-20120326.csv
-rw-r--r-- 1 pi pi 13909 Dez 22 17:38 Bieber_WZ-Spot-20120327.csv
i explain the last two lines
-for "your systems time" 22dec2015 17h38 - your inverter's date 27march2012 last record in the table around 05h20m
-for "your systems time" 22dec2015 12h15 - you will find in that spot table - last record created at 26march timestamp around 23h56m
the above is due to the date-time delta

note: in the spot csv file you will find timestamps for the records equal the time given by the inverter,
note: in the database the timestamp for the spot records are equal to "system" time stamp

-d- for the daily and month tables the default execution by SBFspot corresponds with:
default for daily => -ad1 === today's daily file
default for monthly => -am1 === thismonth's month file
with these default options - sbfspot does interogate your inverter and as conqequence ;-)
-d.1- asks (cfr above example) the daily file for 22dec which results in an empty answer and no file created
-d.2- asks the month file for december 2015 which results in an empty answer and no file is created

-e- your questions
-e.1- What I did not understand is, how often a day the data in the DayData-table is updated (only once or at each call of sbfspot)?
for the databases -> spot a record is added to the database each time you execute sbfspot

for the databases -> day - month each time you execute sbfspot NEW records are added to the database,

if you keep in mind that the default settings for daily and month are "today" and "thismonth"
then you understand that once a day you must execute sbfspot with the options -ad3 and -am2
that single execution will ensure that also the last records of yesterday and the last record of previous month are added to the database

-e.2- What I did not understand either is, when will the csv-files be written? -> two diffferent systems are used
for the spot csv file - at each execution the new record is added
rational: spot data is not kept in memory by the inverter (old data can not be recuperated)
for daily and month file - the csv file is recreated - using the reply form the inverter
rational: the complete daily table and month table is available from the memory of the inverter,
  • the inverter adds a new record into its memory each 5minutes
kind regards wim
Coordinator
Dec 23, 2015 at 9:11 PM
Updating the dates in daydata/monthdata is rather easy, once you know the offset. It's also easy to backup your database: just copy the SBFspot.db file ;-)
All datetimes in the inverter and db are stored in 32 bit unix times (all UTC, no local times) since 1-JAN-1970 (so yes, there will be a problem in the year 2038)
In the month table the time between 2015-12 and 2012-03 is missing. I consider the 2012 data is from 2015-11 and below.
So, in your example, if you want to change dates from 03-2012 to 11-2015
Get UTC unix times using this calculator: http://www.onlineconversion.com/unix_time.htm
1/03/2012 23:55:00 = 1330646100
1/11/2015 23:55:00 = 1446422100
offset = 1446422100 - 1330646100 = 115776000 (1340 days)

Now, in SQLite3 execute something like:
UPDATE MonthData set TimeStamp=TimeStamp+115776000 WHERE TimeStamp<1446422100;
Some warnings:
Didn't test this, so be careful - backup!
Don't forget the "WHERE" clause or you will change ALL records.
Don't re-execute SBFspot to get data in the past or you will re-enter 2012 data in the database
Unfortunately it is not possible to change these values in your inverter
Dec 27, 2015 at 6:09 PM
Thanks to wim; I got the historical csv files (little mistake in -spo --> -sp0 :-)).

After setting the correct converters time, I also got the some moth dates in the moth table (separate Database) with timestamps 01.12.2015, 30.11.2015, 29.11.2015 and so on. But unfortunately the yield values differ from those I got with SBFs way of modifying die original database (with the time gap).

According to SBF I did the following:

First I calculated my time gap:
01.12.2015 23:00:00 = 1449010800 (first record I think with the correct date)
18.03.2012 07:45:00 = 1332056700 (last record with incorrect date)

Instead of 18.03.2015 I want:
30.11.2015 23:00:00 = 1448924400

1448924400 - 1332056700 = 11686770

Unfortunately I got conflicts with unique key constraints (primary key TimeStamp, Serial) while updating with your update command. So first I copied the whole MonthData into a temporary table, updated the values and put them back into the MonthData table.

As far as only DayData is uploaded to PVOutput now it is OK for me.
Jan 1, 2016 at 2:50 PM
Edited Jan 1, 2016 at 11:45 PM
dear jofland, for your information

a simple and accurate way to calculate the "time gap" between the system and the inverter is available in the output log

the method compares info from your system with inverter-time, without needing to extract information from the tables
or to analyse the contents of the tables

the needed step is:
-a-> execute sbfspot with the options -d5 -v5 -sp0 -ad0 -am0 -ae0 -nosql -nocsv -finq
either redirect the output to a file append to the above Command 1>grap-time-gap.txt
or read the information from "screen"

rational: these options reduce the execution to "minimum-minimorum" and avoids creating output

-b-> get from the log file "grap-time-gap.txt" the following Date-Time ifno
-b.1- at "begin" of the log file - you find the "start-Date-Time" at the start of execution of sbfspot
-b.2- at "end" of the log file - you find the Date-Time at the system after execution of sbfspot

-b.3- in the log file you find a line with "current inverter time" (this is one of the last lines)

"Current Inverter Time: 31/12/2015 12:08:59" <-- example

the time gap = "current inverter time" - "start-time"

have fun - enjoy the new year - kind regards wim