Search code examples
mysqldatedatetimedate-comparison

Sql date comparison in where clause is not working as expected

I'm facing a strange mysql behavior...

If I want to return the rows from "MyTable" with a date lower than date-10 seconds ago or a future date I also store future date because in my real program, I "launch" some queries with delay and date is actually the last query date...i.e.: a kind of queue...:

SELECT (NOW() - date) AS new_delay, id 
FROM MyTable 
WHERE (NOW() - date < 10) 
ORDER BY new_delay DESC;

This one does not work as expected: It returns all the entries:

EDIT: here is the result:

enter image description here

However, this one is working just fine:

SELECT (NOW() - date) AS new_delay, id 
FROM MyTable 
WHERE (NOW() < date + 10) 
ORDER BY new_delay DESC;

DB example:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


INSERT INTO `MyTable` (`id`, `date`) VALUES
(1, (NOW())),
(2, (NOW()-10)),
(3, (NOW()+100));

Any ideas??

Solution

  • As proposed by @Gordon in the his answer, I can use the date_sub / date_add functions...

    I can correct the where clause to be :

    WHERE NOW() < date_add(ServerRequests.date, interval 10 second)

    OR

    WHERE date > date_sub(now(), interval 10 second)

    OR as proposed in my initial post:

    WHERE (NOW() < date + 10)

    But I still don't see why I cannot use the sub operation...So if anyone can give me a reason, I would be happy to understand...