[Solved] How to exclude today ( upto 2.30PM ) data row from MYSQL fetch array

Question

Here’s a query that will include the row for today and the rows for the last three days:

SELECT *
FROM ALLRESULTDATA
WHERE
  `date` = CURDATE()
  OR 
  `date` >= DATE_ADD(CURDATE(), INTERVAL -3 DAY)
ORDER BY `date` DESC
LIMIT 4

SQL Fiddle: http://sqlfiddle.com/#!9/88561/3

I think you also mean to only include the result for today if the time is after 14:30, that’s probably possible but it would be clunky in MySQL in my opinion.

I think you would be better grabbing the current time in php and if it’s greater, running the query above and if not, run a different query, e.g. :

SELECT *
FROM ALLRESULTDATA
WHERE
  `date` != CURDATE()
  AND
  `date` >= DATE_ADD(CURDATE(), INTERVAL -3 DAY)
ORDER BY `date` DESC
LIMIT 3

i.e. change the first conditional operator to != and change the OR to an AND and then also change the LIMIT to 3.

Today Excluded Fiddle: http://sqlfiddle.com/#!9/88561/5

EDIT

I just noticed your comment saying that the dates are stored as a VARCHAR(10), if possible I’d recommend changing your schema so that the dates are stored as DATE.

An alternative is the STR_TO_DATE() function, but I don’t think the performance will be particularly nice if you have to resort to using it.

Accepted Answer