BETWEEN - with timestamps
The problem is that 2011-01-31 really is 2011-01-31 00:00:00. That is the beginning of the day.
Everything during the day is not included.

I think it worth noting that, this will not include dates at 2011-01-31 23:59:59 but will include
those up to 2011-01-31 23:59:58 the last second of the day is not included. 

35 total, Query took 0.0058 seconds
35 total, Query took 0.0116 seconds.


7901 total, Query took 0.0522 seconds.)
SELECT * FROM meter
    WHERE serial_number IN(SELECT DISTINCT(SerialNumber) FROM current_devices WHERE org_id_ma = 72)
    AND date_timestamp > '2020-08-02 00:00:00';

8200 total, Query took 4.5389 seconds
SELECT * FROM meter
    WHERE serial_number IN(SELECT DISTINCT(SerialNumber) FROM current_devices WHERE org_id_ma = 72)
    AND date_timestamp < '2020-08-02 00:00:00';

664 total, Query took 1.0629 seconds.
SELECT * FROM meter
    WHERE serial_number IN(SELECT DISTINCT(SerialNumber) FROM current_devices WHERE org_id_ma = 72)
    AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 00:00:00';

7825 total, Query took 0.0748 seconds.
SELECT * FROM meter
    WHERE serial_number IN(SELECT DISTINCT(SerialNumber) FROM current_devices WHERE org_id_ma = 72)
    AND date_timestamp BETWEEN '2020-08-02 00:00:00' AND '2021-06-30 00:00:00';


1 total, Query took 53.5645 seconds
2020-08-01 14:02:56
SELECT MAX(mtr.date_timestamp) FROM meter mtr
    WHERE serial_number IN (SELECT DISTINCT(SerialNumber)
                          	FROM current_devices WHERE org_id_ma = 72)
                            AND date_timestamp < '2020-08-02 00:00:00';


3 total, Query took 54.4053 seconds.
13774099, 13774094, 13774075
SELECT * FROM meter
    WHERE date_timestamp = 
    (SELECT MAX(mtr.date_timestamp) FROM meter mtr
    WHERE serial_number IN (SELECT DISTINCT(SerialNumber)
                          	FROM current_devices WHERE org_id_ma = 72)
                            AND date_timestamp < '2020-08-02 23:59:59');

3 total, Query took 8.3225 seconds.
13774099, 13774094, 13774075
SELECT * FROM meter
    WHERE date_timestamp = 
    (SELECT MAX(mtr.date_timestamp) FROM meter mtr
    WHERE serial_number IN (SELECT DISTINCT(SerialNumber)
                          	FROM current_devices WHERE org_id_ma = 72)
                            AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2020-08-02 23:59:59');



/*We need the MAX date for each of the serial_numbers*/
608 total, Query took 21.5733 seconds.
SELECT * FROM meter
    WHERE date_timestamp = 
    (SELECT MAX(mtr.date_timestamp) FROM meter mtr
    WHERE serial_number IN (SELECT DISTINCT(SerialNumber)
                          	FROM current_devices WHERE org_id_ma = 72)
                            AND date_timestamp BETWEEN '2020-08-02 00:00:00' AND '2021-06-30 23:59:59');


35 total, Query took 24.4544 seconds.
SELECT * FROM meter
WHERE (date_timestamp, serial_number) IN(SELECT MAX(mtr.date_timestamp), mtr.serial_number FROM meter mtr
    										WHERE serial_number IN (SELECT DISTINCT(SerialNumber)
                          											FROM current_devices WHERE org_id_ma = 72)
                            				AND date_timestamp BETWEEN '2020-08-02 00:00:00' AND '2021-06-30 23:59:59'
                                        	GROUP BY serial_number);
    
/*******BEGIN good 8/16/21*****/
SELECT mtr.* FROM meter mtr
        INNER JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number FROM meter mtr
        			WHERE serial_number IN (SELECT SerialNumber FROM current_devices WHERE org_id_ma = 65)
                    AND mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2021-05-15 23:59:58'
                    GROUP BY serial_number) ser_dt ON ser_dt.serial_number = mtr.serial_number
        WHERE date_timestamp = ser_dt.dt;



/*************BETTER ********/
 SELECT  ser_dt.bldg_id, SUM(meter.black_meter) blk_vol_pre_upgrade        
        FROM meter
        
        INNER JOIN (SELECT MAX(mtr.date_timestamp) dt, mtr.serial_number, cd.bldg_id FROM meter mtr
        			INNER JOIN (SELECT SerialNumber, building_id_ma bldg_id FROM current_devices WHERE org_id_ma = 65) cd
                    ON cd.SerialNumber = mtr.serial_number
                    WHERE mtr.date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2021-05-15 23:59:58'
                    GROUP BY mtr.serial_number) ser_dt ON ser_dt.serial_number = meter.serial_number
        WHERE date_timestamp = ser_dt.dt
        GROUP BY ser_dt.bldg_id;
/*******END good 8/16/21*****/



SELECT id, org_name, dateNewUpgrade FROM `organization` WHERE dateNewUpgrade BETWEEN '2020-07-01' AND '2021-06-30';

#select * from buildings where org_id = 65 ORDER BY building_name;

SELECT * FROM meter WHERE serial_number IN(SELECT SerialNumber FROM current_devices WHERE building_id_ma = 1186)
AND date_timestamp BETWEEN '2020-07-01 00:00:00' AND '2021-05-15 23:59:58'
ORDER BY serial_number, date_timestamp DESC;