MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE nmilist (nmi INT UNSIGNED PRIMARY KEY, SiteName VARCHAR(20));
INSERT INTO nmilist VALUES
  (2001767790, 'Pivot 2'),
  (2002172225, 'Dairy'),
  (2002173221, 'Pivot 1');

CREATE TABLE elecdata (
  nmi INT UNSIGNED,
  timestamp DATETIME,
  interval_min TINYINT UNSIGNED,
  rate ENUM('Peak', 'Off-Peak'),
  kWh_con DECIMAL(6, 3),
  kVArh_con DECIMAL(6, 3),
  kWh_ex DECIMAL(6, 3),
  kVArh_ex DECIMAL(6, 3)
);
INSERT INTO elecdata VALUES
  ('2001767790','2024-02-19 23:55:00','5','Off-Peak','5.36','3.03','0','0'),
  ('2002172225','2024-02-19 23:55:00','5','Off-Peak','1.61','0','0','0.02'),
  ('2001767790','2024-02-19 23:50:00','5','Off-Peak','5.38','3.04','0','0'),
  ('2002172225','2024-02-19 23:50:00','5','Off-Peak','1.61','0','0','0.02'),
  ('2001767790','2024-02-19 23:45:00','5','Off-Peak','5.38','3.05','0','0'),
  ('2002172225','2024-02-19 23:45:00','5','Off-Peak','1.62','0','0','0.01'),
  ('2002173221','2024-02-19 23:45:00','15','Off-Peak','20.408','10.976','0','0'),
  ('2001767790','2024-02-19 23:40:00','5','Off-Peak','5.35','3.07','0','0'),
  ('2002172225','2024-02-19 23:40:00','5','Off-Peak','1.64','0','0','0.02'),
  ('2001767790','2024-02-19 23:35:00','5','Off-Peak','5.36','3.11','0','0'),
  ('2002172225','2024-02-19 23:35:00','5','Off-Peak','1.65','0.02','0','0.02'),
  ('2001767790','2024-02-19 23:30:00','5','Off-Peak','5.36','3.11','0','0'),
  ('2002172225','2024-02-19 23:30:00','5','Off-Peak','1.64','0','0','0.01'),
  ('2002173221','2024-02-19 23:30:00','15','Off-Peak','20.392','11.104','0','0'),
  ('2001767790','2024-02-19 23:25:00','5','Off-Peak','5.35','3.09','0','0'),
  ('2002172225','2024-02-19 23:25:00','5','Off-Peak','1.64','0','0','0.02'),
  ('2001767790','2024-02-19 23:20:00','5','Off-Peak','5.36','3.11','0','0'),
  ('2002172225','2024-02-19 23:20:00','5','Off-Peak','1.64','0','0','0.02'),
  ('2001767790','2024-02-19 23:15:00','5','Off-Peak','5.36','3.1','0','0'),
  ('2002172225','2024-02-19 23:15:00','5','Off-Peak','1.64','0','0','0.02'),
  ('2002173221','2024-02-19 23:15:00','15','Off-Peak','20.536','11.208','0','0'),
  ('2001767790','2024-02-19 23:10:00','5','Off-Peak','5.35','3.09','0','0'),
  ('2002172225','2024-02-19 23:10:00','5','Off-Peak','1.64','0','0','0.01'),
  ('2001767790','2024-02-19 23:05:00','5','Off-Peak','5.36','3.1','0','0'),
  ('2002172225','2024-02-19 23:05:00','5','Off-Peak','1.68','0.02','0','0.01'),
  ('2001767790','2024-02-19 23:00:00','5','Off-Peak','5.36','3.1','0','0'),
  ('2002172225','2024-02-19 23:00:00','5','Off-Peak','1.68','0','0','0'),
  ('2002173221','2024-02-19 23:00:00','15','Off-Peak','20.568','11.2','0','0'),
  ('2001767790','2024-02-19 22:55:00','5','Off-Peak','5.36','3.09','0','0'),
  ('2002172225','2024-02-19 22:55:00','5','Off-Peak','1.68','0.01','0','0'),
  ('2001767790','2024-02-19 22:50:00','5','Off-Peak','5.36','3.09','0','0'),
  ('2002172225','2024-02-19 22:50:00','5','Off-Peak','1.68','0','0','0'),
  ('2001767790','2024-02-19 22:45:00','5','Off-Peak','5.37','3.08','0','0'),
  ('2002172225','2024-02-19 22:45:00','5','Off-Peak','1.67','0.01','0','0'),
  ('2002173221','2024-02-19 22:45:00','15','Off-Peak','20.68','11.208','0','0'),
  ('2001767790','2024-02-19 22:40:00','5','Off-Peak','5.35','3.05','0','0'),
  ('2002172225','2024-02-19 22:40:00','5','Off-Peak','1.66','0','0','0'),
  ('2001767790','2024-02-19 22:35:00','5','Off-Peak','5.37','3.06','0','0'),
  ('2002172225','2024-02-19 22:35:00','5','Off-Peak','1.68','0.02','0','0'),
  ('2001767790','2024-02-19 22:30:00','5','Off-Peak','5.35','3.05','0','0'),
  ('2002172225','2024-02-19 22:30:00','5','Off-Peak','1.67','0.01','0','0'),
  ('2002173221','2024-02-19 22:30:00','15','Off-Peak','20.688','11.16','0','0'),
  ('2001767790','2024-02-19 22:25:00','5','Off-Peak','5.35','3.05','0','0'),
  ('2002172225','2024-02-19 22:25:00','5','Off-Peak','1.35','0','0','0'),
  ('2001767790','2024-02-19 22:20:00','5','Off-Peak','5.37','3.05','0','0'),
  ('2002172225','2024-02-19 22:20:00','5','Off-Peak','1.35','0','0','0'),
  ('2001767790','2024-02-19 22:15:00','5','Off-Peak','5.36','3.04','0','0'),
  ('2002172225','2024-02-19 22:15:00','5','Off-Peak','0.37','0.01','0','0.01'),
  ('2002173221','2024-02-19 22:15:00','15','Off-Peak','20.656','11.152','0','0'),
  ('2001767790','2024-02-19 22:10:00','5','Off-Peak','5.37','3.04','0','0'),
  ('2002172225','2024-02-19 22:10:00','5','Off-Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 22:05:00','5','Off-Peak','5.37','3.04','0','0'),
  ('2002172225','2024-02-19 22:05:00','5','Off-Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 22:00:00','5','Off-Peak','5.15','2.97','0','0'),
  ('2002172225','2024-02-19 22:00:00','5','Off-Peak','0.99','0.87','0','0'),
  ('2002173221','2024-02-19 22:00:00','15','Off-Peak','20.296','10.88','0','0'),
  ('2001767790','2024-02-19 21:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:55:00','5','Peak','0.77','0.77','0','0.01'),
  ('2001767790','2024-02-19 21:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:50:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 21:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:45:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 21:45:00','15','Peak','0.424','0.392','0','0'),
  ('2001767790','2024-02-19 21:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:40:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 21:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:35:00','5','Peak','0.09','0','0','0.01'),
  ('2001767790','2024-02-19 21:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:30:00','5','Peak','0.09','0.01','0','0.02'),
  ('2002173221','2024-02-19 21:30:00','15','Peak','0.128','0.08','0','0'),
  ('2001767790','2024-02-19 21:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:25:00','5','Peak','0.08','0','0','0.01'),
  ('2001767790','2024-02-19 21:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:20:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 21:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:15:00','5','Peak','0.08','0.01','0','0.02'),
  ('2002173221','2024-02-19 21:15:00','15','Peak','0.128','0.08','0','0'),
  ('2001767790','2024-02-19 21:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:10:00','5','Peak','0.09','0','0','0.01'),
  ('2001767790','2024-02-19 21:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:05:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 21:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 21:00:00','5','Peak','0.11','0.01','0','0.01'),
  ('2002173221','2024-02-19 21:00:00','15','Peak','0.168','0.112','0','0'),
  ('2001767790','2024-02-19 20:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:55:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 20:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:50:00','5','Peak','0.09','0.01','0','0.01'),
  ('2001767790','2024-02-19 20:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:45:00','5','Peak','0.08','0','0','0.01'),
  ('2002173221','2024-02-19 20:45:00','15','Peak','0.16','0.104','0','0'),
  ('2001767790','2024-02-19 20:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:40:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 20:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:35:00','5','Peak','0.09','0','0','0.01'),
  ('2001767790','2024-02-19 20:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:30:00','5','Peak','0.08','0.01','0','0.02'),
  ('2002173221','2024-02-19 20:30:00','15','Peak','0.16','0.104','0','0'),
  ('2001767790','2024-02-19 20:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:25:00','5','Peak','0.1','0.01','0','0.01'),
  ('2001767790','2024-02-19 20:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:20:00','5','Peak','0.09','0','0','0.01'),
  ('2001767790','2024-02-19 20:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:15:00','5','Peak','0.09','0','0','0.02'),
  ('2002173221','2024-02-19 20:15:00','15','Peak','0.128','0.088','0','0'),
  ('2001767790','2024-02-19 20:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:10:00','5','Peak','0.08','0.01','0','0.01'),
  ('2001767790','2024-02-19 20:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:05:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 20:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 20:00:00','5','Peak','0.1','0.01','0','0.01'),
  ('2002173221','2024-02-19 20:00:00','15','Peak','0.176','0.112','0','0'),
  ('2001767790','2024-02-19 19:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:55:00','5','Peak','0.1','0.01','0','0.02'),
  ('2001767790','2024-02-19 19:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:50:00','5','Peak','0.08','0','0','0.01'),
  ('2001767790','2024-02-19 19:45:00','5','Peak','0.01','0','0','0'),
  ('2002172225','2024-02-19 19:45:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 19:45:00','15','Peak','0.176','0.112','0','0'),
  ('2001767790','2024-02-19 19:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:40:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 19:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:35:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 19:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:30:00','5','Peak','0.08','0.01','0','0.02'),
  ('2002173221','2024-02-19 19:30:00','15','Peak','0.128','0.072','0','0'),
  ('2001767790','2024-02-19 19:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:25:00','5','Peak','0.09','0.01','0','0.01'),
  ('2001767790','2024-02-19 19:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:20:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 19:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:15:00','5','Peak','0.09','0','0','0.01'),
  ('2002173221','2024-02-19 19:15:00','15','Peak','0.424','0.288','0','0'),
  ('2001767790','2024-02-19 19:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:10:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 19:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:05:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 19:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 19:00:00','5','Peak','0.1','0','0','0.01'),
  ('2002173221','2024-02-19 19:00:00','15','Peak','0.656','0.424','0','0'),
  ('2001767790','2024-02-19 18:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:55:00','5','Peak','0.11','0','0','0.02'),
  ('2001767790','2024-02-19 18:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:50:00','5','Peak','0.12','0.02','0','0.02'),
  ('2001767790','2024-02-19 18:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:45:00','5','Peak','0.11','0','0','0.01'),
  ('2002173221','2024-02-19 18:45:00','15','Peak','0.64','0.432','0','0'),
  ('2001767790','2024-02-19 18:40:00','5','Peak','0','0','0','0.01'),
  ('2002172225','2024-02-19 18:40:00','5','Peak','0.1','0','0','0.02'),
  ('2001767790','2024-02-19 18:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:35:00','5','Peak','0.11','0','0','0.01'),
  ('2001767790','2024-02-19 18:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:30:00','5','Peak','0.11','0','0','0.02'),
  ('2002173221','2024-02-19 18:30:00','15','Peak','0.408','0.28','0','0'),
  ('2001767790','2024-02-19 18:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:25:00','5','Peak','0.12','0','0','0.01'),
  ('2001767790','2024-02-19 18:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:20:00','5','Peak','0.12','0.02','0','0.02'),
  ('2001767790','2024-02-19 18:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:15:00','5','Peak','0.15','0.03','0','0.01'),
  ('2002173221','2024-02-19 18:15:00','15','Peak','0.648','0.44','0','0'),
  ('2001767790','2024-02-19 18:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:10:00','5','Peak','1.08','0.93','0','0'),
  ('2001767790','2024-02-19 18:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:05:00','5','Peak','2.62','2.39','0','0'),
  ('2001767790','2024-02-19 18:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 18:00:00','5','Peak','1.22','1.07','0','0'),
  ('2002173221','2024-02-19 18:00:00','15','Peak','0.52','0.36','0','0'),
  ('2001767790','2024-02-19 17:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:55:00','5','Peak','2.27','1.8','0','0'),
  ('2001767790','2024-02-19 17:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:50:00','5','Peak','2.73','1.84','0','0'),
  ('2001767790','2024-02-19 17:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:45:00','5','Peak','3.03','2.45','0','0'),
  ('2002173221','2024-02-19 17:45:00','15','Peak','0.592','0.416','0','0'),
  ('2001767790','2024-02-19 17:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:40:00','5','Peak','3.58','2.56','0','0'),
  ('2001767790','2024-02-19 17:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:35:00','5','Peak','3.64','2.59','0','0'),
  ('2001767790','2024-02-19 17:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:30:00','5','Peak','3.39','2.74','0','0'),
  ('2002173221','2024-02-19 17:30:00','15','Peak','0.384','0.264','0','0'),
  ('2001767790','2024-02-19 17:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:25:00','5','Peak','3.82','2.81','0','0'),
  ('2001767790','2024-02-19 17:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:20:00','5','Peak','3.69','2.78','0','0'),
  ('2001767790','2024-02-19 17:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:15:00','5','Peak','3.86','2.6','0','0'),
  ('2002173221','2024-02-19 17:15:00','15','Peak','0.272','0.184','0','0'),
  ('2001767790','2024-02-19 17:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:10:00','5','Peak','3.81','2.72','0','0'),
  ('2001767790','2024-02-19 17:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:05:00','5','Peak','3.85','2.67','0','0'),
  ('2001767790','2024-02-19 17:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 17:00:00','5','Peak','3.84','2.57','0','0'),
  ('2002173221','2024-02-19 17:00:00','15','Peak','0.224','0.144','0','0'),
  ('2001767790','2024-02-19 16:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:55:00','5','Peak','4','2.72','0','0'),
  ('2001767790','2024-02-19 16:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:50:00','5','Peak','4','2.63','0','0'),
  ('2001767790','2024-02-19 16:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:45:00','5','Peak','3.98','2.65','0','0'),
  ('2002173221','2024-02-19 16:45:00','15','Peak','0.272','0.192','0','0'),
  ('2001767790','2024-02-19 16:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:40:00','5','Peak','4','2.65','0','0'),
  ('2001767790','2024-02-19 16:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:35:00','5','Peak','4.11','2.79','0','0'),
  ('2001767790','2024-02-19 16:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:30:00','5','Peak','3.99','2.64','0','0'),
  ('2002173221','2024-02-19 16:30:00','15','Peak','0.24','0.16','0','0'),
  ('2001767790','2024-02-19 16:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:25:00','5','Peak','4.03','2.64','0','0'),
  ('2001767790','2024-02-19 16:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:20:00','5','Peak','3.94','2.63','0','0'),
  ('2001767790','2024-02-19 16:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:15:00','5','Peak','3.95','2.64','0','0'),
  ('2002173221','2024-02-19 16:15:00','15','Peak','0.32','0.216','0','0'),
  ('2001767790','2024-02-19 16:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:10:00','5','Peak','4.08','2.73','0','0'),
  ('2001767790','2024-02-19 16:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:05:00','5','Peak','3.15','2.02','0','0'),
  ('2001767790','2024-02-19 16:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 16:00:00','5','Peak','2.96','1.81','0','0'),
  ('2002173221','2024-02-19 16:00:00','15','Peak','0.36','0.256','0','0'),
  ('2001767790','2024-02-19 15:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:55:00','5','Peak','3.94','2.67','0','0'),
  ('2001767790','2024-02-19 15:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:50:00','5','Peak','3.95','2.67','0','0'),
  ('2001767790','2024-02-19 15:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:45:00','5','Peak','3.22','2.2','0','0'),
  ('2002173221','2024-02-19 15:45:00','15','Peak','0.16','0.104','0','0'),
  ('2001767790','2024-02-19 15:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:40:00','5','Peak','1.49','0.63','0','0'),
  ('2001767790','2024-02-19 15:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:35:00','5','Peak','1.17','0.36','0','0'),
  ('2001767790','2024-02-19 15:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:30:00','5','Peak','1.2','0.35','0','0'),
  ('2002173221','2024-02-19 15:30:00','15','Peak','0.128','0.08','0','0'),
  ('2001767790','2024-02-19 15:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:25:00','5','Peak','1.02','0.27','0','0'),
  ('2001767790','2024-02-19 15:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:20:00','5','Peak','1.04','0.27','0','0'),
  ('2001767790','2024-02-19 15:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:15:00','5','Peak','1.06','0.28','0','0'),
  ('2002173221','2024-02-19 15:15:00','15','Peak','0.176','0.12','0','0'),
  ('2001767790','2024-02-19 15:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:10:00','5','Peak','1','0.27','0','0'),
  ('2001767790','2024-02-19 15:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:05:00','5','Peak','0.68','0.31','0','0'),
  ('2001767790','2024-02-19 15:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 15:00:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 15:00:00','15','Peak','0.456','0.32','0','0'),
  ('2001767790','2024-02-19 14:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:55:00','5','Peak','0.07','0','0','0.02'),
  ('2001767790','2024-02-19 14:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:50:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 14:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:45:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 14:45:00','15','Peak','0.304','0.208','0','0'),
  ('2001767790','2024-02-19 14:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:40:00','5','Peak','0.09','0.02','0','0.02'),
  ('2001767790','2024-02-19 14:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:35:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 14:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:30:00','5','Peak','0.08','0','0','0.01'),
  ('2002173221','2024-02-19 14:30:00','15','Peak','0.336','0.24','0','0'),
  ('2001767790','2024-02-19 14:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:25:00','5','Peak','0.09','0','0','0.02'),
  ('2001767790','2024-02-19 14:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:20:00','5','Peak','1.23','0','0','0.02'),
  ('2001767790','2024-02-19 14:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:15:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 14:15:00','15','Peak','0.4','0.288','0','0'),
  ('2001767790','2024-02-19 14:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:10:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 14:05:00','5','Peak','0','0','0','0.01'),
  ('2002172225','2024-02-19 14:05:00','5','Peak','0.08','0.01','0','0.02'),
  ('2001767790','2024-02-19 14:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 14:00:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 14:00:00','15','Peak','0.432','0.312','0','0'),
  ('2001767790','2024-02-19 13:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:55:00','5','Peak','0.07','0.01','0','0.01'),
  ('2001767790','2024-02-19 13:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:50:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 13:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:45:00','5','Peak','0.07','0','0','0.02'),
  ('2002173221','2024-02-19 13:45:00','15','Peak','0.136','0.088','0','0'),
  ('2001767790','2024-02-19 13:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:40:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 13:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:35:00','5','Peak','0.09','0.01','0','0.02'),
  ('2001767790','2024-02-19 13:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:30:00','5','Peak','0.07','0','0','0.01'),
  ('2002173221','2024-02-19 13:30:00','15','Peak','0.136','0.088','0','0'),
  ('2001767790','2024-02-19 13:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:25:00','5','Peak','0.08','0','0','0.02'),
  ('2001767790','2024-02-19 13:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:20:00','5','Peak','0.08','0.01','0','0.02'),
  ('2001767790','2024-02-19 13:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:15:00','5','Peak','0.08','0','0','0.02'),
  ('2002173221','2024-02-19 13:15:00','15','Peak','0.128','0.08','0','0'),
  ('2001767790','2024-02-19 13:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:10:00','5','Peak','0.07','0','0','0.02'),
  ('2001767790','2024-02-19 13:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:05:00','5','Peak','0.09','0.02','0','0.01'),
  ('2001767790','2024-02-19 13:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 13:00:00','5','Peak','0.13','0.01','0','0.01'),
  ('2002173221','2024-02-19 13:00:00','15','Peak','0.128','0.088','0','0'),
  ('2001767790','2024-02-19 12:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:55:00','5','Peak','0.13','0.01','0','0'),
  ('2001767790','2024-02-19 12:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:50:00','5','Peak','0.09','0.01','0','0.01'),
  ('2001767790','2024-02-19 12:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:45:00','5','Peak','0.12','0.01','0','0.01'),
  ('2002173221','2024-02-19 12:45:00','15','Peak','0.168','0.104','0','0'),
  ('2001767790','2024-02-19 12:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:40:00','5','Peak','0.17','0.01','0','0'),
  ('2001767790','2024-02-19 12:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:35:00','5','Peak','0.21','0.05','0','0'),
  ('2001767790','2024-02-19 12:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:30:00','5','Peak','1.43','0.05','0','0'),
  ('2002173221','2024-02-19 12:30:00','15','Peak','0.136','0.088','0','0'),
  ('2001767790','2024-02-19 12:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:25:00','5','Peak','1.47','0.04','0','0'),
  ('2001767790','2024-02-19 12:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:20:00','5','Peak','1.43','0.02','0','0'),
  ('2001767790','2024-02-19 12:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:15:00','5','Peak','1.39','0.01','0','0'),
  ('2002173221','2024-02-19 12:15:00','15','Peak','0.2','0.136','0','0'),
  ('2001767790','2024-02-19 12:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:10:00','5','Peak','1.4','0.01','0','0'),
  ('2001767790','2024-02-19 12:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:05:00','5','Peak','1.35','0.01','0','0.02'),
  ('2001767790','2024-02-19 12:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 12:00:00','5','Peak','1.37','0.01','0','0.01'),
  ('2002173221','2024-02-19 12:00:00','15','Peak','0.528','0.384','0','0'),
  ('2001767790','2024-02-19 11:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:55:00','5','Peak','1.35','0','0','0.02'),
  ('2001767790','2024-02-19 11:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:50:00','5','Peak','1.34','0','0','0.02'),
  ('2001767790','2024-02-19 11:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:45:00','5','Peak','1.34','0','0','0.02'),
  ('2002173221','2024-02-19 11:45:00','15','Peak','0.68','0.464','0','0'),
  ('2001767790','2024-02-19 11:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:40:00','5','Peak','1.35','0','0','0.02'),
  ('2001767790','2024-02-19 11:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:35:00','5','Peak','1.34','0','0','0.02'),
  ('2001767790','2024-02-19 11:30:00','5','Peak','0.01','0','0','0'),
  ('2002172225','2024-02-19 11:30:00','5','Peak','1.35','0.01','0','0.02'),
  ('2002173221','2024-02-19 11:30:00','15','Peak','0.672','0.464','0','0'),
  ('2001767790','2024-02-19 11:25:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:25:00','5','Peak','1.34','0.01','0','0.02'),
  ('2001767790','2024-02-19 11:20:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:20:00','5','Peak','2.95','1.7','0','0'),
  ('2001767790','2024-02-19 11:15:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:15:00','5','Peak','1.35','0','0','0.02'),
  ('2002173221','2024-02-19 11:15:00','15','Peak','0.68','0.456','0','0'),
  ('2001767790','2024-02-19 11:10:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:10:00','5','Peak','1.34','0','0','0.02'),
  ('2001767790','2024-02-19 11:05:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:05:00','5','Peak','1.33','0','0','0.02'),
  ('2001767790','2024-02-19 11:00:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 11:00:00','5','Peak','1.34','0','0','0.02'),
  ('2002173221','2024-02-19 11:00:00','15','Peak','0.672','0.464','0','0'),
  ('2001767790','2024-02-19 10:55:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 10:55:00','5','Peak','1.35','0.01','0','0.02'),
  ('2001767790','2024-02-19 10:50:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 10:50:00','5','Peak','1.33','0','0','0.02'),
  ('2001767790','2024-02-19 10:45:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 10:45:00','5','Peak','1.34','0','0','0.02'),
  ('2002173221','2024-02-19 10:45:00','15','Peak','0.68','0.456','0','0'),
  ('2001767790','2024-02-19 10:40:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 10:40:00','5','Peak','1.33','0.01','0','0.02'),
  ('2001767790','2024-02-19 10:35:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 10:35:00','5','Peak','1.34','0','0','0.02'),
  ('2001767790','2024-02-19 10:30:00','5','Peak','0','0','0','0'),
  ('2002172225','2024-02-19 10:30:00','5','Peak','1.33','0','0','0.02'),
  ('2002173221','2024-02-19 10:30:00','15','Peak','0.208','0.144','0','0'),
  ('2001767790','2024-02-19 10:25:00','5','Peak','0','0','0','0');

Records: 3  Duplicates: 0  Warnings: 0



Records: 379  Duplicates: 0  Warnings: 0


SELECT elecdata.nmi as nmi, rate, nmilist.SiteName AS site, DATE(timestamp) AS datee, SUM(kWh_con) AS kwh
FROM elecdata, nmilist
WHERE elecdata.nmi = nmilist.nmi AND rate='Peak' AND DATE(timestamp) = (CURRENT_DATE() - 1)
GROUP BY elecdata.nmi, datee;

SELECT elecdata.nmi as nmi, rate, nmilist.SiteName AS site, DATE(timestamp) AS datee, SUM(kWh_con) AS kwh
FROM elecdata, nmilist
WHERE elecdata.nmi = nmilist.nmi AND rate='Off-Peak' AND DATE(timestamp) = (CURRENT_DATE() - 1)
GROUP BY elecdata.nmi, datee;

SELECT elecdata.nmi as nmi, nmilist.SiteName AS site, DATE(timestamp) AS datee, SUM(kWh_con) AS kwh
FROM elecdata, nmilist
WHERE elecdata.nmi = nmilist.nmi AND DATE(timestamp) = (CURRENT_DATE() - 1)
GROUP BY elecdata.nmi, datee;


nmi
rate
site
datee
kwh


2001767790
Peak
Pivot 2
2024-02-19
0.020

2002172225
Peak
Dairy
2024-02-19
156.770

2002173221
Peak
Pivot 1
2024-02-19
15.280




nmi
rate
site
datee
kwh


2001767790
Off-Peak
Pivot 2
2024-02-19
128.460

2002172225
Off-Peak
Dairy
2024-02-19
33.960

2002173221
Off-Peak
Pivot 1
2024-02-19
164.224




nmi
site
datee
kwh


2001767790
Pivot 2
2024-02-19
128.480

2002172225
Dairy
2024-02-19
190.730

2002173221
Pivot 1
2024-02-19
179.504


SELECT
    elecdata.nmi AS NMI,
    nmilist.SiteName AS SiteName,
    DATE(timestamp) AS Date,
    SUM(IF(rate='Off-Peak', kWh_con, 0)) AS `OffPeak Use`,
    SUM(IF(rate='Peak', kWh_con, 0)) AS `Peak Use`,
    SUM(kWh_con) AS Total
FROM elecdata
JOIN nmilist ON elecdata.nmi = nmilist.nmi
WHERE timestamp >= (CURRENT_DATE - INTERVAL 1 DAY)
AND timestamp < CURRENT_DATE
GROUP BY elecdata.nmi, Date;


NMI
SiteName
Date
OffPeak Use
Peak Use
Total


2001767790
Pivot 2
2024-02-19
128.460
0.020
128.480

2002172225
Dairy
2024-02-19
33.960
156.770
190.730

2002173221
Pivot 1
2024-02-19
164.224
15.280
179.504
nmi	rate	site	datee	kwh
2001767790	Peak	Pivot 2	2024-02-19	0.020
2002172225	Peak	Dairy	2024-02-19	156.770
2002173221	Peak	Pivot 1	2024-02-19	15.280
nmi	rate	site	datee	kwh
2001767790	Off-Peak	Pivot 2	2024-02-19	128.460
2002172225	Off-Peak	Dairy	2024-02-19	33.960
2002173221	Off-Peak	Pivot 1	2024-02-19	164.224