Arun Kumaresh Arun Kumaresh - 5 months ago 13
SQL Question

mysql how to retrieve the data based on time?

i need to retrieve the data from table based on the time and date i.e i need to get the data from yesterday 5.00pm to current day till 5pm .I have tried the following code in which i reterived the yesterday and today but with time it is not working help to solve this problem note:the column modified is in timestamp

SELECT * FROM `mdl_forum_posts`
where from_unixtime(modified,'%Y-%m-%d')=CURDATE()
or from_unixtime(modified,'%Y-%m-%d')=date(CURDATE()-1)

Answer
SELECT * FROM `mdl_forum_posts`
modified BETWEEN UNIX_TIMESTAMP(
           CONCAT(DATE_FORMAT(CURDATE()-1, '%Y-%m-%d'), ' 17:00:00'))
         AND UNIX_TIMESTAMP(
           CONCAT(CURDATE(), ' 17:00:00'));

Note that CURDATE() is polymorphic; the DATE_FORMAT is required for the first predicate where the calculation forces an integer-like return type.

Wrapping attributes in functions within predicates disables the use of indexes - applying the inverse function to the literal allows the for use of indexes (and even in the absence of indexes will be faster as the function need only be evaulated once).