CODEWITHSUNDEEP
phpmysqldatedatetimetime
Dan Bennett
Dan Bennett

Reputation: 17

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

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?

Upvotes: 0

Views: 5699

Answers (4)

Brian Putt
Brian Putt

Reputation: 1348

Why not try:

$from_date = date("Y-m-d", strtotime("2013-12-11"))." 00:00:00";
$to_date = date("Y-m-d", strtotime("2013-12-12")." 11:59:59";

select * from data where timeplayed <= $to_date and timeplayed >= $from_date

Upvotes: 1

Rob M.
Rob M.

Reputation: 36511

I think that you should rethink your approach. All of the Date functions in MySQL are geared toward the formats it expects. It would be much easier to do this in the presentation layer as:

echo date('H:i:sa | d-m-Y', strtotime($row['timeplayed']);

This will allow you to use all of the MySQL functions as expected.

Upvotes: 1

MonkeyZeus
MonkeyZeus

Reputation: 20737

Due to the custom date, your only savior is this query:

$sql = "SELECT * FROM data WHERE timeplayed LIKE '%".date("d-m-Y")."'";

This will yield horrid performance as your table grows...

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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;

Upvotes: 4

Related Questions