1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'General PHP and MySQL Discussions' started by sadiq6210, Jan 12, 2016.

  1. sadiq6210

    sadiq6210 Active Member

    Hi,

    I need to call some data from database for yesterday, I wrote this:
    Code:
    $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?
     
  2. HWS

    HWS Well-Known Member

    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 likes this.
  3. sadiq6210

    sadiq6210 Active Member

    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
     
  4. Mouth

    Mouth Well-Known Member

    Code:
    WHERE last_download_date BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()
     
    sadiq6210 likes this.
  5. sadiq6210

    sadiq6210 Active Member

    Not working, the counter is showing only zero.
     
  6. Mouth

    Mouth Well-Known Member

    It was an example. last_download_date is integer unix time, so you need to convert ...
    Code:
    WHERE FROM_UNIXTIME(last_download_date) BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()
     
    sadiq6210 likes this.
  7. sadiq6210

    sadiq6210 Active Member

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

Share This Page