CODEWITHSUNDEEP
phpmysqlsql
Muhammad Faizan Khan
Muhammad Faizan Khan

Reputation: 10571

How to compare time in mysql

I have a column of Time data type in mysql. I want to compare the time with a time string but don't know how to compare it. e.g., I want to know that some specific time is greater than 10:30:00. How can I check it? I have this query but it is not showing any result although the data is available. what I am doing wrong?

select PK, userID, lr.Date, lr.Time, Half, lr.InOut, Op_UserID, About
from loginrecord lr
where lr.Date Between '2017-05-17' AND '2017-05-17'
and lr.InOut = 1
and lr.Time > '10:30:00'

Upvotes: 9

Views: 18832

Answers (3)

Roman Kotov
Roman Kotov

Reputation: 1159

Also direct access to hour, minutes and seconds can be used to compare values 

select PK, userID, lr.Date, lr.Time, Half, lr.InOut, Op_UserID, About
from loginrecord lr
where lr.Date Between '2017-05-17' AND '2017-05-17'
and lr.InOut = 1
and HOUR(lr.Time) > 10
and MINUTE(lr.Time) > 30
and SECOND(lr.Time) > 00

For further information, please refer to MYSQL Date and time funcitons

Upvotes: 2

AkshayP
AkshayP

Reputation: 2159

Try this

select PK, userID, lr.Date, lr.Time, Half, lr.InOut, Op_UserID, About
from loginrecord lr
where lr.Date Between '2017-05-17' AND '2017-05-17'
and lr.InOut = 1
and lr.Time > CAST('10:30:00' AS time)

The CAST() function converts a value of any type into a value that has a specified type. The target type can be any one of the following types: BINARY, CHAR, DATE, DATETIME, TIME,DECIMAL, SIGNED, UNSIGNED .

The CAST() function is often used to return a value with a specified type for comparison in the WHERE, JOIN, and HAVING clauses.

Reference

Upvotes: 9

Guillaume
Guillaume

Reputation: 153

You can use the TIME_FORMAT function, like this:

select PK, userID, lr.Date, lr.Time, Half, lr.InOut, Op_UserID, About
from loginrecord lr
where lr.Date Between '2017-05-17' AND '2017-05-17'
and lr.InOut = 1
and TIME_FORMAT(lr.Time, '%H:%i') > '10:30'

Upvotes: 7

Related Questions