如何从MYSQL获取数组中排除今天（最多2.30PM）数据行

Question

In MySql...

How to exclude today date ( upto 2.30PM on each day ) data row from MYSQL fetch array.. How to publish today result + last 3 using PHP + mysql ...

Sample Table : ALLRESULTDATA

id  price   date 
1   Rs.50   2015-12-09

2   Rs.5    2015-12-10

3   Rs.52   2015-12-11

4   Rs.55   2015-12-12  
5   Rs.53   2015-12-13  
6   Rs.53   2015-12-14

How to fetch last 3 days result ( Exclude today result upto 2.30PM afterthat publish TODAY)

mysql_query("select * from ALLRESULTDATA desc limit 4");

Answer 1

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.