This project has moved and is read-only. For the latest updates, please go here.

vwSpotData.Efficiency

Topics: 4. Bugs
Sep 24, 2014 at 9:46 AM
I’m looking in the SPBspot database version 3.0.1 and trying to get things out. It is a nice work to do, and I’m learning every day. Thanks!

So I found in vwSpotData the field Efficiency. In my database it has the only values “0”, “0.0” or “100.0”. Nothing else. I checked the formula in the view and that seem to be all right. Please check this one.

Thanks for the reply.
Frans
Sep 24, 2014 at 12:24 PM
On MySQL it's OK, but SQLite does integer math causing this issue
It's fixed in the unreleased 3.0.2
Sep 24, 2014 at 3:35 PM
Edited Sep 24, 2014 at 5:38 PM
This is the new 3.0.2 view
Efficiency, BT_Signal and Temperature are rounded to 0.1
Efficiency is also limited to 100% in case Pdc > Pac (when low power)
DROP VIEW IF EXISTS vwSpotData;

CREATE View vwSpotData AS
SELECT datetime(Dat.TimeStamp, 'unixepoch', 'localtime') TimeStamp, 
    datetime(CASE WHEN (Dat.TimeStamp % 300) < 150
    THEN Dat.TimeStamp - (Dat.TimeStamp % 300)
    ELSE Dat.TimeStamp - (Dat.TimeStamp % 300) + 300
    END, 'unixepoch', 'localtime') AS Nearest5min,
    Inv.Name,
    Inv.Type,
    Dat.Serial,
    Pdc1,Pdc2,
    Idc1,Idc2,
    Udc1,Udc2,
    Pac1,Pac2,Pac3,
    Iac1,Iac2,Iac3,
    Uac1,Uac2,Uac3,
    Pdc1+Pdc2 AS PdcTot,
    Pac1+Pac2+Pac3 AS PacTot,
    CASE WHEN Pdc1+Pdc2 = 0 THEN
        0
    ELSE
        CASE WHEN Pdc1+Pdc2>Pac1+Pac2+Pac3 THEN
            ROUND(1.0*(Pac1+Pac2+Pac3)/(Pdc1+Pdc2)*100,1)
        ELSE
            100.0
        END
    END AS Efficiency,
    Dat.EToday,
    Dat.ETotal,
    Frequency,
    Dat.OperatingTime,
    Dat.FeedInTime,
    ROUND(BT_Signal,1) AS BT_Signal,
    Dat.Status,
    Dat.GridRelay,
    ROUND(Dat.Temperature,1) AS Temperature
    FROM [SpotData] Dat
INNER JOIN Inverters Inv ON Dat.Serial = Inv.Serial
ORDER BY Dat.Timestamp Desc;
Marked as answer by SBF on 9/24/2014 at 7:38 AM
Sep 24, 2014 at 4:18 PM
Thanks,
Works great! (now ;-) )
I do not understand your TIP. For your information: I run the SQL-scripts in SqliteBrowser.
Sep 24, 2014 at 5:41 PM
I do not understand your TIP.
Now it looks OK, All '+' characters were replaced by their hex representation