user2114103 - 1 year ago 82
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.

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download