Arun Kumaresh Arun Kumaresh - 1 year ago 76
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 Source
SELECT * FROM `mdl_forum_posts`
           CONCAT(DATE_FORMAT(CURDATE()-1, '%Y-%m-%d'), ' 17:00:00'))
           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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download