SQL date issue ?

Topics: 4. Bugs, 5. Support
Feb 17, 2016 at 10:36 AM
Hi,

After issue #147 being solved, my output with a rapsberry pi to pvoutput.org, seems to work.

However, I do have some other questions, if possible. Reason I'm asking the following, is because I'm writing some specific things, based on the SBFspot's SQL results for my personal use.
  1. If I'm not mistaken, the upload to pvoutput.org is done with the Plantname-Spot-date.csv ? That could be the only reason why they show the correct output...
  2. Why exactly are the default csv's, Plantname-date.csv, still created ?
  3. The monthly generated csv is one day behind ... so is the SQL data, it seems. I can resolve this by adding 1 day in my code, but I was wondering if this known or if there's a purpose for this ? Because it will screw up monthly results as well.
    Eg : for one inverter, the correct value for 15/02 : 7,071 kWh (read with Sunny Explorer and live at the SMA itself)
    plant-spot-date.csv
    15/02 7,071 kWh
    monthly created csv
    14/02 7,071 kWh
    15/02 8,054 kWh
    SQL
    2016-02-14 23:00:00 SB 2500HF-30 9374243 7071
    2016-02-15 23:00:00 SB 2500HF-30 9382297 8054
    I've seen in the config file that the timesource=inverter, but as you can see, that one seems to be correct. I've also reset the plant time to be sure.
I noticed, that the archivedaydata() function starts at 00:00:00 , not sure if this could be a second-issue, creating a day's difference ?

Any clues, tips are welcome !



Thanks !
Bart
Feb 18, 2016 at 12:00 PM
Edited Feb 18, 2016 at 9:16 PM
dear bart, herafter a point-point reply for your questions,

to assist in handling your questions, we'd like te obtain from you the following information

?1? . . . .
-a- please provide your configuration file(s) -> sbfspot.cfg and the other sbf**.cfg files
as you can find on the website, when users of sbfspot do have remarks or issues that they should execute sbfspot
by adding the "debug" options -d5 -v5 to the command line -> the cfg file is part of the output with these options

-b- please provide the command-line string(s) that you apply when executing sbfspot
there are two options in the command line (and similar switches in the cfg file) TO INHIBIT (select) data output: -nocsv -nosql
by default SBFspot will write "DATA" to both "ACTIVE" outputs - active output destinations ae specified in the sbfspot.cfg file

?2? Why exactly are the default csv's, Plantname-date.csv, still created ? <- i expect the answer is in your cfg file

?3? The monthly generated csv is one day behind
this is a pending bug, the cause of this issue is under investigation !!! the mismatch is not allways there

note: this issue should not have effect for the pv-output -> but it is a problem for local analysis of your data
  • in post-processing analysis, i apply a work around (assumption is that for a single day there is ALLWAYS some production
    I'll read the first record for daily information (5min-interval) and compare that TOTAL"kWh counter" with the day records in the month file,
    by that compare i do know whether, for the day records, YES /NO - I must apply an offset with one day
?4? time-setting is also a pending issue -> in the earlier versions of SBFspot that did work perfect,
it is still under investigation which command is to be changed to get this correct
we do recommend that users: -verify and set the correct time for the inverter at least twice a year

?5? the parameter "time-source" selects whether SBFspot applies
  • the "local-time from inverter for the spot-reocrds"
  • your local PC (or RPI) system time
kind regards wim
Feb 18, 2016 at 2:10 PM
Edited Feb 18, 2016 at 2:13 PM
Hi Wim,

Seems like i can't attach the requested files here.
I've sent them through mail.

The workaround I had, was a little different, but I assumed this would Always be a problem.
Making your suggestion kind of... better :)

Ow yeah ... the cronjob that gets executed... I used the exact same command as instructed :
/usr/local/bin/sbfspot.3/SBFspot -v
Same for the interval
*/5 6-23 * * * /home/pi/scripts/SBFspot.sh

The time source should be the inverter, according to the cfg file.
I can change the plant time, but it seems I can't adjust the daylight saving option.


Thanks for checking the files !

Bart
Feb 18, 2016 at 9:43 PM
Edited Feb 18, 2016 at 10:29 PM
dear Bart, i did answer to your private mail - pitty but i can not recup your files

about the time source, be aware that this selector ONLY applies to SPOT reocrds
-- records (DB or .csv) in the daily table (5min interval) are "inverter-time", records are created by inverter-software at the 5min tic
-- for records (DB or .csv) in the spot table - the user can choice (to be in line with daily table the default = "inverter-time")

about the DayLightSaving, I do not have a labo with all types/variants of inverter,
-- to modify this "choice" IN THE INVERTER you must use SunnyExplorer (windows!!)
-- in my setup - i was able to select for my SunnyBoy to operate without DLS
(be aware the best moment to modify is in Winter) because with that change the inverter might overwrite some records
(i mean, if you life in australia this is to be done in may--aug period)
-- it depends on the usage of YOUR System, but in my case the server that I also use for SBFspot
"is ALLWAYS on Wintertime"

about scheduling and cronjob this point has been in discussion by several persons on the forum
-- if you apply DLS and your house is in Belgium then the cron can be set
*/5 6-20 * * * /home/pi/scripts/SBFspot.sh

Rational: in belgium the sun passes the meridian around 12h46m
-- so 6-20 is 6h45 before "SUN-high" and 7h15 after "SUN-high"
one can not diretly indicate 19h30m for the cron - one needs to define a second cron task
this fits for 1may-15aug suntransit
-- the general document for SBFspot assumes DLS = ON hence covers the longer suntransit in summer

kind regards wim
Feb 19, 2016 at 12:32 PM
Edited Feb 19, 2016 at 12:34 PM
WillieSimons wrote:
dear Bart, i did answer to your private mail - pitty but i can not recup your files

about the time source, be aware that this selector ONLY applies to SPOT reocrds
-- records (DB or .csv) in the daily table (5min interval) are "inverter-time", records are created by inverter-software at the 5min tic
-- for records (DB or .csv) in the spot table - the user can choice (to be in line with daily table the default = "inverter-time")
The dates for the daily table (5min interval) ARE correct, so I'm assuming that the time is well read from both inverters.
It's just the monthly dates that aren't processed correctly. All this being said, and your given information, it won't be an inverter setting causing that, I guess
about the DayLightSaving, I do not have a labo with all types/variants of inverter,
Totally understand, didn't really suggest you should have to :)
-- to modify this "choice" IN THE INVERTER you must use SunnyExplorer (windows!!)
I did ... But the DLS options is just greyed out, no matter what I try or how i login. But it is DISABLED, so that's a good thing i guess
-- in my setup - i was able to select for my SunnyBoy to operate without DLS
(be aware the best moment to modify is in Winter) because with that change the inverter might overwrite some records
(i mean, if you life in australia this is to be done in may--aug period)
-- it depends on the usage of YOUR System, but in my case the server that I also use for SBFspot
"is ALLWAYS on Wintertime"

about scheduling and cronjob this point has been in discussion by several persons on the forum
-- if you apply DLS and your house is in Belgium then the cron can be set
*/5 6-20 * * * /home/pi/scripts/SBFspot.sh

Rational: in belgium the sun passes the meridian around 12h46m
-- so 6-20 is 6h45 before "SUN-high" and 7h15 after "SUN-high"
one can not diretly indicate 19h30m for the cron - one needs to define a second cron task
this fits for 1may-15aug suntransit
-- the general document for SBFspot assumes DLS = ON hence covers the longer suntransit in summer

kind regards wim
In general, good info. Thanks !
I've sent you the requested files by mail.

PS: How exactly does the daily purge into "monthly data" happen in DB ? Based on the output, i guess when SBFspot runs, it gets the data from the inverters, its not an SQL query... A few things i did notice in the output file i have sent you
on line 3223/3224 :
monthDataOffset=-86400
monthDataOffset=-86400
2 times 24h could explain why the date's behind ?
For the output made on the 18/02 , the latest dates that can be found in the log are :
Inverter 1 : <3399> 16/02/2016 : 6972.418kWh - 7.717kWh
Inverter 2 : <3417> 16/02/2016 : 9390.147kWh - 7.850kWh
So I guess the next in line will have to be 17/02, which get filled with the output data of 18/02 ?

Just guessing around here. Your knowledge of this code is by far better, cause you created it hehe :)


Best regards,
Bart
Feb 21, 2016 at 12:41 PM
dear bart,

-a- I'll get in touch with sbf for your remarks AND for your original demands in this discussion

-b- from your configuration file - i have not found a reason for this reactions

-c- read my reply in your text AND thanks for your remarks

a first point to be aware:
  • the internal "database" of the inverter has three parts/tables:
    -- daily = 5min interval records (62days of records are kept, oldest are dropped)
    -- month = end-of-day records (an history of >>2000days)
    -- events = (>500records are stored, drop-logic ??) - with sections for "user" and "installer"
  • the inverters treat the 5min-interval-records independent from end-day-records
  • the extraction of the tables from inverter by sbfspot works independent (daily - monthly -events)
  • the inverter does NOT have a SPOT table
kind regards wim


Bart83 wrote:
WillieSimons wrote:
dear Bart, i did answer to your private mail - pitty but i can not recup your files

about the time source, be aware that this selector ONLY applies to SPOT reocrds
-- records (DB or .csv) in the daily table (5min interval) are "inverter-time", records are created by inverter-software at the 5min tic
-- for records (DB or .csv) in the spot table - the user can choice (to be in line with daily table the default = "inverter-time")
The dates for the daily table (5min interval) ARE correct, so I'm assuming that the time is well read from both inverters.
It's just the monthly dates that aren't processed correctly. All this being said, and your given information, it won't be an inverter setting causing that, I guess
about the DayLightSaving, I do not have a labo with all types/variants of inverter,
Totally understand, didn't really suggest you should have to :)
-- to modify this "choice" IN THE INVERTER you must use SunnyExplorer (windows!!)
I did ... But the DLS options is just greyed out, no matter what I try or how i login. But it is DISABLED, so that's a good thing i guess
???did you login as "installer" or only as "user"
???since you're having two inverters, depending onyour setup - sunny-explorer might only act towards the COUPLE of two,

-- in my setup - i was able to select for my SunnyBoy to operate without DLS
(be aware the best moment to modify is in Winter) because with that change the inverter might overwrite some records
(i mean, if you life in australia this is to be done in may--aug period)
-- it depends on the usage of YOUR System, but in my case the server that I also use for SBFspot
"is ALLWAYS on Wintertime"

about scheduling and cronjob this point has been in discussion by several persons on the forum
-- if you apply DLS and your house is in Belgium then the cron can be set
*/5 6-20 * * * /home/pi/scripts/SBFspot.sh

Rational: in belgium the sun passes the meridian around 12h46m
-- so 6-20 is 6h45 before "SUN-high" and 7h15 after "SUN-high"
one can not diretly indicate 19h30m for the cron - one needs to define a second cron task
this fits for 1may-15aug suntransit
-- the general document for SBFspot assumes DLS = ON hence covers the longer suntransit in summer

kind regards wim
In general, good info. Thanks !
I've sent you the requested files by mail.

PS: How exactly does the daily purge into "monthly data" happen in DB ? Based on the output, i guess when SBFspot runs, it gets the data from the inverters, its not an SQL query... A few things i did notice in the output file i have sent you
on line 3223/3224 :
monthDataOffset=-86400
monthDataOffset=-86400
2 times 24h could explain why the date's behind ?
!!!! good remark - we will be checking for this

For the output made on the 18/02 , the latest dates that can be found in the log are :
Inverter 1 : <3399> 16/02/2016 : 6972.418kWh - 7.717kWh
Inverter 2 : <3417> 16/02/2016 : 9390.147kWh - 7.850kWh
So I guess the next in line will have to be 17/02, which get filled with the output data of 18/02 ?

Just guessing around here. Your knowledge of this code is by far better, cause you created it hehe :)


Best regards,
Bart
Feb 21, 2016 at 3:02 PM
Edited Feb 22, 2016 at 7:49 PM
Hi Wim,

I somehow have a feeling we're messing up this posts' layout, so I took out the main parts :)

WillieSimons wrote:
dear bart,
-a- I'll get in touch with sbf for your remarks AND for your original demands in this discussion
Bart83 : Thanks, but no pressure of course, i realize this is an opensource project :)
.
WillieSimons wrote:
a first point to be aware:
...
  • the internal "database" of the inverter has three parts/tables:
    -- daily = 5min interval records (62days of records are kept, oldest are dropped)
    -- month = end-of-day records (an history of >>2000days)
    -- events = (>500records are stored, drop-logic ??) - with sections for "user" and "installer"
  • the inverters treat the 5min-interval-records independent from end-day-records
  • the extraction of the tables from inverter by sbfspot works independent (daily - monthly -events)
  • the inverter does NOT have a SPOT table
    Bart83 : Yep, I noticed the three parts/tables
.
WillieSimons wrote:
It's just the monthly dates that aren't processed correctly. All this being said, and your given information, it won't be an inverter setting causing that, I guess
Bart83 : Same thoughts
.
WillieSimons wrote:
???did you login as "installer" or only as "user"
Bart83 wrote: Tried both of 'm, logically it would be the installer to use, but with both the DLS option is just greyed out, I can set the time, timezone and push it to the inverters... But the options not being available is more a SMA problem than it has to do with SBFspot

???since you're having two inverters, depending on your setup - sunny-explorer might only act towards the COUPLE of two,
Bart83 wrote: Values are being read seperately, but can also be seen through Sunny Explorer in a "merged" view, same for SBFspot, does the job perfect as far as I've seen in the past few weeks.
.
WillieSimons wrote:
monthDataOffset=-86400
monthDataOffset=-86400
2 times 24h could explain why the date's behind ?
!!!! good remark - we will be checking for this
Bart83 wrote: I've added a timestamp column to check when the record was made. It's not on a fixed scheduled date I see, but depending on the inverters start up time
Time the record was added 2016-02-21 07:30:15 returns
  • timestamp 1455922800 (monthdata) : according to online converter and my timezone : ‎20‎/‎02‎/‎2016‎ ‎0‎:‎00‎:‎00 (previous day which is correct)
  • timestamp 2016-02-19 23:00:00 (vwmonthdata)
Took another one to double check, but don't have a record-made-timestamp on that one
  • timestamp 1455231600 (monthdata) : according to online converter and my timezone : ‎‎12‎/‎02‎/‎2016‎ ‎0‎:‎00‎:‎00
  • timestamp 2016-02-11 23:00:00 (vwmonthdata)
So as far as I can tell, it's a small bug with the conversion of the str to date in the vwmonthdata

Timezone settings
Timezone inverter : UTC/+1 Brussels
Linux : Sun Feb 21 16:00:05 CET 2016
MySQL : @@global.time_zone: SYSTEM
MySQL : @@session.time_zone: SYSTEM
Thanks again, and please let me know if you want me to check something!

Best regards,
Bart
Feb 22, 2016 at 7:59 PM
Edited Feb 22, 2016 at 8:00 PM
Took another look into the code. As it seems to be a problem with the str to date conversion, i suppose the problem is within the creation of the vwmonthdata (CreateMySQLDB.sql)

CREATE View vwMonthData AS
select convert_tz(from_unixtime(Dat.TimeStamp), 'SYSTEM', '+00:00') 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;

I'm not that an sql pro, so haven't figured out the convert_tz yet completely, but that has to be the problematic part. Especially when I see the 'system' over there, makes the view dependant on UTC location. And since you told me that the problem is not occuring always, makes perfect sense if the timezone doesn't get setup correctly, but as far as I know, my PI system time is correct.

I'll experiment some more with these view settings so I can hopefully help you guys out !


Grtz
Bart
Coordinator
Feb 23, 2016 at 9:30 AM
Edited Feb 23, 2016 at 9:54 AM
I'm struggling with this bug since a long time, and I know it's caused by the inverters.
Take the raw dates (retrieved from inverter) in the MonthData table and convert them using this online tool
You'll see the time part is not always 23:55:00. Sometimes it's 00:55:00 (resulting in 1 day later)
The monthDataOffset=-86400 you see in the log is part of an attempt to solve it, but it's still not working correctly. It might be solved in SBFspot 4, but this version is not yet ready to release.
The same date shifts are visible in the csv files and in SQLite, so I'm pretty sure there is no issue with the MontData view.

See also this thread
Feb 23, 2016 at 1:15 PM
SBF wrote:
I'm struggling with this bug since a long time, and I know it's caused by the inverters.
Take the raw dates (retrieved from inverter) in the MonthData table and convert them using this online tool
You'll see the time part is not always 23:55:00. Sometimes it's 00:55:00 (resulting in 1 day later)
The monthDataOffset=-86400 you see in the log is part of an attempt to solve it, but it's still not working correctly. It might be solved in SBFspot 4, but this version is not yet ready to release.
The same date shifts are visible in the csv files and in SQLite, so I'm pretty sure there is no issue with the MontData view.

See also this thread
It's a strange one, please let me know if I can help you test something !
Coordinator
May 31, 2016 at 4:19 PM
The monthdata shift is fixed in V3.1.4 (See issue 130)
Marked as answer by SBF on 5/31/2016 at 8:26 AM