Getting last 24 hours data from MySQL in PHP with custom date string

Questions

I m adding a custom date string to the database by using;

$date = date("H:i:sa | d-m-Y");
$sql = "INSERT INTO data
        (artist, title, presenter, timeplayed)
        VALUES( $artist , $title , $presenter , $date )";

However I can t then quite figure out how to get $date back out but only the last 24 hours worth of entries. So between 00:00:00 yesterday and 23:59:59 today.

I ve tried:

SELECT * FROM data WHERE timeplayed > DATE_SUB( NOW(), INTERVAL 24 HOUR)

And similar, however it s bringing back data older than 24 hours.

I m storing the data in timeplayed as varchar. I wonder if that s the reason?

Answers

As per above comments you should save the data as proper mysql date format and to get the data of last 24 hours you can use the inerval of day subtracting from NOW()

SELECT * FROM data WHERE timeplayed >= NOW() - INTERVAL 1 DAY;

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/20555018/getting-last-24-hours-data-from-mysql-in-php-with-custom-date-string

Related