rounding up and down in mysql query NULLs results
I have to calculate every half hour that a student departs after 15:00. However, I also have some grace periods: <0:15 = 0 half hour, >16 = 1 half hour.
My query was working until this requirement presented itself. Now HalfHours and Amount are both null:
INSERT INTO `attendence` (`did`, `sid`, `arrived`, `departed`) VALUES (16655, 60, 20, '2023-09-08 09:25:00', '2023-09-08 14:54:00'), (16673, 60, 20, '2023-09-11 09:36:00', '2023-09-11 16:00:00'), (16694, 60, 20, '2023-09-12 09:27:00', '2023-09-12 16:17:00'), (16713, 60, 20, '2023-09-13 09:21:00', '2023-09-13 15:35:00'), (16735, 60, 20, '2023-09-14 09:52:00', '2023-09-14 15:00:00'), (16754, 60, 20, '2023-09-15 09:19:00', '2023-09-15 15:20:00'), (16783, 60, 20, '2023-09-19 09:27:00', '2023-09-19 16:23:00'), (16799, 60, 20, '2023-09-20 09:22:00', '2023-09-20 15:58:00'), (16816, 60, 20, '2023-09-21 09:14:00', '2023-09-21 14:54:00'), (16842, 60, 20, '2023-09-22 09:24:00', '2023-09-22 15:58:00'), (16859, 60, 20, '2023-09-25 09:24:00', '2023-09-25 14:59:00'), (16884, 60, 20, '2023-09-26 09:19:00', '2023-09-26 16:16:00'), (16928, 60, 20, '2023-09-28 09:30:00', '2023-09-28 14:51:00'), (16948, 60, 20, '2023-09-29 09:33:00', '2023-09-29 15:15:00');SELECT
s.sid,
CONCAT(s.name, ' ', s.lastname) AS FullName,
COUNT(*) AS AfterschoolCount,
GROUP_CONCAT(DATE_FORMAT(a.departed, ' %d|%H:%i') ORDER BY a.departed ASC) AS Dates,
SUM(
CASE
WHEN a.departed IS NOT NULL THEN
CASE
WHEN MINUTE(a.departed) % 30 >= 16 THEN FLOOR((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) + 1799) / 1800)
WHEN MINUTE(a.departed) % 30 <= 15 THEN CEIL((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) - 899) / 1800)
ELSE FLOOR((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) + 899) / 1800)
END
ELSE 0
END
) AS HalfHours,
SUM(
CASE
WHEN a.departed IS NOT NULL THEN
CASE
WHEN MINUTE(a.departed) % 30 >= 16 THEN FLOOR((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) + 1799) / 1800) * 500
WHEN MINUTE(a.departed) % 30 <= 15 THEN CEIL((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) - 899) / 1800) * 500
ELSE FLOOR((TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) + 899) / 1800) * 500
END
ELSE 0
END
) AS Amount
FROM
students s
JOIN
attendence a ON s.sid = a.sid
WHERE
TIME(a.departed) > '15:00:00' AND
MONTH(a.departed) = 9 AND
YEAR(a.arrived) = 2023 AND
s.active = 'Yes'
GROUP BY
s.sid, FullName
ORDER BY
AfterschoolCount DESC
if i get it right, the first 15 minutes are free and between 30 and 45 minutes only counts 1 half hour and so on
why not simply ?
FLOOR( TIME_TO_SEC(TIMEDIFF(a.departed, '15:15:00')) / 1800 )or simpler ?
ROUND( TIME_TO_SEC(TIMEDIFF(a.departed, '15:00:00')) /1800 )ASKER
Thanks so much PortletPaul, the first one seems to be working well. But, I'm not sure it's doing the < 15, >16 rounding. For example, looking at sid 127, it looks like it would be 4 half hours, not 5.
The 2nd one you provided doesn't seem to work as well. Maybe it's my limited understanding.
ASKER
I think I did it with this:
SUM(
CASE
WHEN a.departed IS NOT NULL THEN
CASE
WHEN HOUR(a.departed) > 15 OR (HOUR(a.departed) = 15 AND MINUTE(a.departed) >= 16) THEN
-- Round up, considering the minute
CEIL((TIME_TO_SEC(a.departed) - TIME_TO_SEC('15:00:00')) / 1800)
WHEN HOUR(a.departed) = 15 AND MINUTE(a.departed) < 16 THEN
-- Round down, considering the minute
FLOOR((TIME_TO_SEC(a.departed) - TIME_TO_SEC('15:00:00')) / 1800)
ELSE 0
END
ELSE 0
END
) AS HalfHoursseems overcomplicated.
once you expressed this in halve hours, rounding does the same. the rounding issue will last one second rather than one minute. you can ignore it or just use timediff -1. exit times before 15:30 need to be filtered out.
What data type have you used for columns `arrived`, `departed` ?
I used timestamp in my example, and in v8 of MySQL if I run
TIMEDIFF(a.departed, '15:00:00')
I get NULL
But, If I run
TIMEDIFF(DATE_FORMAT(a.departed, '%H:%m:%s'), '15:00:00')
then I get a non-null result
In the original question, as the calculated columns were NULL, it seemed TIMEDIFF was the cause of the NULLs, and this is why I recommended EXTRACT
| did | sid | arrived | departed | ROUND( TIME_TO_SEC(TIMEDIFF(time(a.departed), '15:00:00')) /1800 ) |
|---|---|---|---|---|
| 16655 | 6020 | 2023-09-08 09:25:00 | 2023-09-08 14:54:00 | 0 |
| 16673 | 6020 | 2023-09-11 09:36:00 | 2023-09-11 16:00:00 | 2 |
| 16694 | 6020 | 2023-09-12 09:27:00 | 2023-09-12 16:17:00 | 3 |
| 16713 | 6020 | 2023-09-13 09:21:00 | 2023-09-13 15:35:00 | 1 |
| 16735 | 6020 | 2023-09-14 09:52:00 | 2023-09-14 15:00:00 | 0 |
| 16754 | 6020 | 2023-09-15 09:19:00 | 2023-09-15 15:20:00 | 1 |
| 16783 | 6020 | 2023-09-19 09:27:00 | 2023-09-19 16:23:00 | 3 |
| 16799 | 6020 | 2023-09-20 09:22:00 | 2023-09-20 15:58:00 | 2 |
| 16816 | 6020 | 2023-09-21 09:14:00 | 2023-09-21 14:54:00 | 0 |
| 16842 | 6020 | 2023-09-22 09:24:00 | 2023-09-22 15:58:00 | 2 |
| 16859 | 6020 | 2023-09-25 09:24:00 | 2023-09-25 14:59:00 | 0 |
| 16884 | 6020 | 2023-09-26 09:19:00 | 2023-09-26 16:16:00 | 3 |
| 16928 | 6020 | 2023-09-28 09:30:00 | 2023-09-28 14:51:00 | 0 |
| 16948 | 6020 | 2023-09-29 09:33:00 | 2023-09-29 15:15:00 | 1 |
| 16948 | 6020 | 2023-09-29 09:33:00 | 2023-09-29 15:14:59 | 0 |
slight correction since i thought your departed date was a time rather than a date.
i used your own query for reference which produced the same issue you had in the first place.
thanks the fiddle for helping me notice.
nb : my query will happily report minus something if the student left early. depending on the use case, that could matter
--
if you know the date of the exam (replace YYYY-MM-DD in the below query).
round(TIMESTAMPDIFF(SECOND, 'YYYY-MM-DD 15:00:00', a.departed) / 1800 )
ASKER
I agree on the 'overcomplicated', but it does give the results I was expecting.
Thanks for the help.
Hi.
I would make the calculation and rounding using server side code this will simplify the SQL query.
So use the SQL get the data from BD and use server side code do the calculation.