Sometimes Sometimes - 2 years ago 70
SQL Question

How can I use DATEDIFF() to return rows where it has been more than 24 hours since it was added

I have table with column called

date_added
stored as datetime in MySQL.
I want to return rows where it has been 24 hours or more since it was added to the database.

I'm using the following query. However, it doesn't return what I want it to return.

SELECT * FROM campaign WHERE datediff(date_added,NOW())>=1


Here's what
date_added
in the Database looks like:
2017-08-15 00:48:31

Answer Source

You can do it this way

//This code is for SQL
SELECT *, DATEDIFF(HOUR,date_added,NOW()) AS Hours 
FROM campaign  WHERE DATEDIFF(HOUR,date_added,NOW()) >=24

As you can see i made the diff by hour

UPDATED

Try this one below.

SELECT * FROM campaign  
WHERE HOUR(TIMEDIFF(DATE_FORMAT(date_added, "%Y-%m-%d %H:%i:%s"), DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s"))) >= 24

I used TimeDiff and used dateformat and made it to hour.

You can also used this query

 SELECT * FROM campaign WHERE TIMESTAMPDIFF(HOUR, date_added, NOW()) >= 24
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download