CODEWITHSUNDEEP
mysqldatabase
Noble Dinasaur
Noble Dinasaur

Reputation: 107

MySql select using time comparison

For my project, I need to select rows from this table

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| hardware_type_id | int(11)      | NO   | MUL | NULL    |                |
| make_type_id     | int(11)      | NO   | MUL | NULL    |                |
| year             | int(11)      | YES  |     | NULL    |                |
| model            | varchar(255) | YES  |     | NULL    |                |
| name             | varchar(255) | NO   |     | NULL    |                |
| warranty         | date         | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

where the warranty is still valid at the time of search. (If warranty is null, then do not select that row)

Basically I need something like this:

SELECT * FROM hardware WHERE ({now} - warranty) <0; // valid warranty
SELECT * FROM hardware WHERE ({now} - warranty) >0; // invalid warranty

Upvotes: 0

Views: 46

Answers (3)

Jester
Jester

Reputation: 1408

Like Mihai already said

SELECT * FROM hardware WHERE warranty <=  CURDATE(); // valid warranty
SELECT * FROM hardware WHERE warranty >  CURDATE() OR warranty IS NULL; // invalid warranty

This should work, you can also use NOW() or CURTIME() if it's datetime or a time type

Upvotes: 1

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

if(warranty > (now()-warranty))
then 
select * from hardware where warranty > (now()-warranty)   ;
end if ;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use DATEDIFF like this:

SELECT *, 
       IF(DATEDIFF(NOW(), warranty) > 0, false, true) AS IsWarrantyValid
FROM hardware

If DATEDIFF(NOW(), warranty) > 0 this means that warranty is before current date, hence it has expired. Thus the IF expression returns false in this case.

Upvotes: 0

Related Questions