CODEWITHSUNDEEP
mysqldatetime
snowflakes74
snowflakes74

Reputation: 1307

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.

Upvotes: 0

Views: 87

Answers (1)

Galz
Galz

Reputation: 6832

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')

Upvotes: 1

Related Questions