CODEWITHSUNDEEP
phphtmlmysqlsqldatetime
Shane Buckley
Shane Buckley

Reputation: 49

PHP select data from previous 24 hours sql database

I was using the script below to pull data from previous 24hrs and spit it out in a html table, it worked for the whole month of August but then messed up since the month changed to September and is now displaying all data from first entry..

My 'Timestamp' column is varchar + primary key and is formatted like so: 04/09/2014 01:33:59

This is the script: 

$sql = "SELECT * 
        FROM my_table
        WHERE Timestamp >= DATE_FORMAT(CURDATE(), '%d/%m/%Y') 
        ORDER BY Timestamp ";

What is wrong with this script? I just want it to pull all data from the past 24 hours and that is all, thanks in advance!

Upvotes: 1

Views: 137

Answers (2)

Barmar
Barmar

Reputation: 780724

Instead of converting CURDATE() to a string, you should convert Timestamp to a DATE. You can use STR_TO_DATE for this.

WHERE STR_TO_DATE(Timestamp, '%d/%m/%Y %H:%i:%s') >= CURDATE()
ORDER BY STR_TO_DATE(Timestamp, '%d/%m/%Y %H:%i:%s')

You could also use your original query, but just use = instead of >=. You just want rows from the current date, not rows in the future.

Upvotes: 1

Shen Shen
Shen Shen

Reputation: 72

If in PHP, i use diff

$sql = "SELECT timestamp from yourtable";
$now = new DateTime();
if($r = mysql_query($sql))
{
   while($row = mysql_fetch_array($r))
   {
      $dt = date("Y-m-d H:i:s",strtotime($row['timestamp']));
      $dates = new DateTime($dt);
      $datedif = $now->diff($dates)->format("%d");
      if($datedif >= 1)
      {
           Your events
      }
   }
}

Hope this helps you

Upvotes: 0

Related Questions