user2114103 user2114103 - 5 months ago 22
MySQL Question

MySQL Query to rollup data based on shiftwise for days in month

We have a table of content like below,

Timestamp | Total_Value
2015-06-05 07:14:23 ------------568
2015-06-06 15:14:23 ------------768
2015-06-07 15:14:23 ------------868
2015-06-08 19:47:30 ------------968
2015-06-30 19:47:30 ------------1668
2015-07-01 06:47:30 ------------268


shifts starts from Morning 7 AM of current day to next day morning 7 AM. Totally three shifts. So per day data calculation requires data of next day till 7 AM.

SELECT DAY(TIMESTAMP) AS DAY, MAX(Total_Value) AS `Total`
FROM Table_NAME
WHERE MONTH(TIMESTAMP) = MONTH(NOW())
GROUP BY DAY(TIMESTAMP)
ORDER BY DAY


Above query provides the data for individual days properly for the timestamp between 2015-06-01 00:00:00 to 2015-06-30 23:59:59

But i need the data for a month between 2015-06-01 07:00:00 to 2015-07-01 06:59:59 in the form of

Day --------Total_Value
1 ------------0
2 ------------0
3 -------------0
4 -------------0
5 ------------568
6 ------------768
7 ------------868
8 ------------968

.
.
.
.
30 ------------1768


2) Also need MySQL query to break a day into 3 shifts where hours of shifts are 7AM to 3:30PM, 3:30PM to 11:00PM and 11PM to 7:00AM(next day) and to be grouped for a week.

Please help resolve this issues. Thanks in advance.

Answer

Subtract 7 hours from the timestamp to adjust it into the proper day for the shift.

SELECT DAY(DATE_SUB(timestamp, INTERVAL 7 HOUR)) AS Day, MAX(total_value) AS Total
FROM Table_Name
WHERE MONTH(DATE_SUB(timestamp, INTERVAL 7 HOUR)) = MONTH(NOW())
GROUP BY Day
ORDER BY Day

To break it into shifts, use a CASE expression to determine the shift

SELECT DAY(DATE_SUB(timestamp, INTERVAL 7 HOUR)) AS Day, 
        CASE WHEN DATE_FORMAT(timestamp, '%H:%i') < '07:00' THEN 3
             WHEN DATE_FORMAT(timestamp, '%H:%i') < '15:30' THEN 1
             WHEN DATE_FORMAT(timestamp, '%H:%i') < '23:00' THEN 2
             ELSE 3
        END AS Shift,
        MAX(total_value) AS Total
FROM Table_Name
WHERE MONTH(DATE_SUB(timestamp, INTERVAL 7 HOUR)) = MONTH(NOW())
GROUP BY Day, Shift
ORDER BY Day, Shift
Comments