snowflakes74 snowflakes74 - 3 months ago 8
MySQL Question

MySQL data in 15 Minutes Increments

I am trying to create high charts time line graph for my school attendance but I get slightly different records appearing in my result.

The table that stores attendance is :

+++++++++++++++++++++++++++++++++
Id | Date | AttendTime(varchar)
--------------------------------
1 | 20160815 | 7:51
2 | 20160815 | 7:53
3 | 20160815 | 8:01
-------------------------------


I am using following sql to get the data

SELECT
COUNT(Id) as Total
FROM Attendance
WHERE Date = '20160815' AND
HOUR(STR_TO_DATE(AttendTime, '%H:%i')) <= HOUR(STR_TO_DATE('7:30', '%H:%i'))


And I should get 0 as there are none but I get 3 records. What am I doing wrong?
Thank you.

Answer

If you are simply trying to see how many attendances were before 7:30, you do not need the HOUR function:

SELECT 
     COUNT(Id) as Total 
FROM Attendance 
     WHERE Date = '20160815' 
     AND STR_TO_DATE(AttendTime, '%H:%i') <= STR_TO_DATE('7:30', '%H:%i')