I need to be able to select all records with a timestamp in the last x number of hours.

There are several tasks I'm facing which require this type of comparison. In the first instance, I need to erase all user accounts that haven't been verified after 24 hours. For another table I need to do the same thing, but with 48 hours. And for the admin panel I'm building for myself, I want to see all users who have logged in in the last 24 hours.

After googling and (of course) reading the manuals I've ended up with something like this, which doesn't seem to work accurately ("timestamp" is the column name, as well as the field type):

"SELECT DISTINCT username FROM log_login WHERE timestamp > (DATE_ADD(CURDATE(), INTERVAL -24 HOUR))"

I think I just need someone to spell it out for me.

Is it best to use timestamp or datetime? Of course I'd like to do all the work with the SQL statement, and not PHP.

Eagerly awaiting assistance,
Phaedrus

Whenever you refer to the column called timestamp, I'd enclose it in backticks just so there is no confusion.

I tend to store dates in UNSIGNED INT(10)s and populate them using the PHP date() function, though this is just personal preference.

Wow, that was harder than I anticipated. I did finally get it, though. I used a date/time field and used this SQL statement to select records within the last 24 hours:

SELECT * FROM log_login WHERE time_field >= ADDDATE(NOW(),INTERVAL -24 HOUR)

I changed the field name, but that didn't end up having anything to do with it.

Maybe that will help someone else