Call the data of yesterday (00:00 hrs to 00:00 hrs)

[Sadiq6210]

Hi,

I need to call some data from database for yesterday, I wrote this:

$db->fetchOne('
        SELECT COUNT(resource_download_id) AS total
        FROM xf_resource_download
        WHERE last_download_date > ' . $lastDay . '

But ($lastDay) is calling the data based on "last 24 hours", while I want from yesterday at 00:00 hrs to today at 00:00 hrs, so the counter will update the value at 00:00 hrs from each day.

Any idea guys?

 

[HWS]

WHERE last_download_date >UNIX_TIMESTAMP_START AND last_download_date < UNIX_TIMESTAMP_END

Replace UNIX_TIMESTAMP_START and UNIX_TIMESTAMP_END with the correct timestamps.

Get the timestamps calculated here:
http://www.epochconverter.com/

 

[Sadiq6210]

WHERE last_download_date >UNIX_TIMESTAMP_START AND last_download_date < UNIX_TIMESTAMP_END

Replace UNIX_TIMESTAMP_START and UNIX_TIMESTAMP_END with the correct timestamps.

Get the timestamps calculated here:
http://www.epochconverter.com/

Thanks but I don't want to put specific date (one date) (i.e 13-01-2016 to 14-01-2016)
Date should be variable so the counter will be auto updated every 24 hours

 

[Mouth]

WHERE last_download_date BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()

 

[Sadiq6210]

WHERE last_download_date BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()

Not working, the counter is showing only zero.

 

[Mouth]

Not working, the counter is showing only zero.

It was an example. last_download_date is integer unix time, so you need to convert ...

WHERE FROM_UNIXTIME(last_download_date) BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()

 

[Sadiq6210]

It was an example. last_download_date is integer unix time, so you need to convert ...

WHERE FROM_UNIXTIME(last_download_date) BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()

Work like charm!
Thank you very much for your help.