jQuerybeast jQuerybeast - 7 months ago 9
SQL Question

SQL: Select * WHERE due date and due time after NOW() without DATETIME()

I am trying to select * tasks that are not due. That been said, anything thats past this exact date and time should be selected taking in consideration that I have a separate columns for date and time.

Currently I am using this where it does not select all instances of today:

SELECT * FROM `tasks` WHERE `due_date` >= NOW() AND `due_time` >= NOW()


I have no access in alternating the database for a DATETIME field. I can only select.

The due_date has DATE as type and due_time has TIME as type

Answer

To get the behavior it seems like you're looking for, you could do something like this:

  SELECT t.* FROM `tasks` t
   WHERE t.`due_date` >= DATE(NOW())
     AND (  ( t.`due_date` = DATE(NOW()) AND t.`due_time` >= TIME(NOW()) )
         OR ( t.`due_date` > DATE(NOW()) )
         )

The first cut is the comparison to due_date, all tasks that are due today or later. That includes too many, we need to get rid of tasks that are due today but before the current time.

There are other query approaches that may seem "simpler". The approach above keeps the predicates on bare columns, so MySQL can make effective use of range scan operations on suitable indexes.

FOLLOWUP

I've done this type of query before, for paging with multiple columns in the key. Something was bothering me about that original query, it had more conditions than were really required. (What threw me off was the >= condition on due_time.)

I believe this is equivalent:

  SELECT t.* FROM `tasks` t
   WHERE t.`due_date` >= DATE(NOW())
     AND ( t.`due_date` > DATE(NOW()) OR t.`due_time` >= TIME(NOW()) )
Comments