Checking if mySql datetime is older then 1 day from php now()

Question

Hi, I have a record returned from MySQL that has a datetime field. What I want to do is take this value and see if it is older then 24 hours, I presume using PHP's time() to get the current time.

At the moment if I echo them out I get:

 1276954824            this is php's time()
 2010-06-19 09:39:23   this is the MySQL datetime

I presume the top one is a unix time? Have been playing around with strtotime but with not much success..

ANy help welcome!

Answer 1

Why are you mixing PHP times and MySQL times?

Instead, do the comparison directly in MySQL:

To get the current date/time in MySQL use the NOW() function. You can compare, for example, 2010-06-19 09:39:23' < DATE_SUB(NOW(), INTERVAL 1 DAY)

This would check to see if the given date (presumably in a column) is older than 24 hours.

If it's absolutely necessary to convert a MySQL timestamp to a UNIX timestamp, you can use MySQL's UNIX_TIMESTAMP() function to do so.

Answer 2

No success?

echo strtotime("2010-06-19 09:39:23");

gives me

1276940363

(mktime(9, 39, 23, 6, 19, 2010) gives the same time, so the parsing works correctly)

---

To get the differences in seconds, you can substract the timestamps, e.g.

$diff = time() - strtotime("2010-06-19 09:39:23");

If the differences is larger than 86400 (60*60*24) seconds, then the timestamps are more than one day apart:

if(time() - strtotime("2010-06-19 09:39:23") > 60*60*24) {
   // timestamp is older than one day
}

Answer 3

You can also do:

SELECT * FROM my_table WHERE timestamp < NOW() - INTERVAL 1 DAY;