Hours between certain times
I'm trying to adjust the below script to charge specific rates between certain hours during the day.
Mid-peak rate 7:00am - 11:00am cost is 11.4 cents/kwh
On-peak rate 11:00am - 5:00pm cost is 14 cents/kwh
Mid-peak rate 5:00pm - 7:00pm cost is 11.4 cents/kwh
Off-peak rate 7:00pm - 7:00am cost is 7.7 cents/kwh
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 2 WEEK )
GROUP BY M.NAME
Does anyone know if this is possible?