31 lines
1.1 KiB
SQL
31 lines
1.1 KiB
SQL
BEGIN TRANSACTION;
|
|
|
|
-- Alter metric columns
|
|
ALTER TABLE SolarPanelOutput RENAME COLUMN Watts TO CurrentWatts;
|
|
ALTER TABLE SolarPanelOutput ADD COLUMN TotalWatts BIGINT;
|
|
UPDATE SolarPanelOutput SET TotalWatts = CAST(ROUND(KilowattHour * 1000) AS BIGINT);
|
|
ALTER TABLE SolarPanelOutput DROP COLUMN KilowattHour;
|
|
|
|
ALTER TABLE SolarPanelSummary ADD COLUMN TotalWatts BIGINT;
|
|
UPDATE SolarPanelSummary SET TotalWatts = CAST(ROUND(Kilowatthour * 1000) AS BIGINT);
|
|
ALTER TABLE SolarPanelSummary DROP COLUMN Kilowatthour;
|
|
|
|
-- Optimize indice
|
|
CREATE INDEX idx_TotalWatts ON SolarPanelOutput (TotalWatts);
|
|
|
|
-- Fix date bug
|
|
UPDATE SolarPanelOutput SET Date = '2018-02-02' WHERE Date = '18-02-02';
|
|
|
|
-- Remove bogus data
|
|
DELETE FROM SolarPanelOutput WHERE Date = '2018-02-02' AND TimeUtc = '08:15:10';
|
|
DELETE FROM SolarPanelOutput WHERE Date = '2018-01-02' AND TimeUtc = '13:31:03';
|
|
|
|
-- Copy data
|
|
INSERT INTO ZeverLogs (Date, TimeUtc, CurrentWatts, TotalWatts) SELECT * FROM SolarPanelOutput;
|
|
INSERT INTO ZeverSummary (Date, TotalWatts) SELECT * FROM SolarPanelSummary;
|
|
|
|
-- Delete old table
|
|
DROP TABLE SolarPanelOutput;
|
|
DROP TABLE SolarPanelSummary;
|
|
|
|
COMMIT; |