Given that one of the columns in a MySQL table represents a timestamp, I cant seem to find the correct query to select the rows from a database table where the timestamps column is less then 5 minutes old.
How do I only select rows where the timestamp column is within 5 minutes of the current time?
I was able to figure out that the following MySQL query will return all rows where a timestamp column called my_timestamp occurred in the last 5 minutes:
SELECT *
FROM my_table
WHERE
my_timestamp > DATE_SUB(now(), INTERVAL 5 MINUTE)
NOW() - 300
doesn't to it for you ? 🤔
now() -x number
— champi0n
Ah, NOW returns a non-UNIX timestamp.
UNIX_TIMESTAMP() - 300
And come to think of it, you're probably not storing timestamps as UNIX timestamps are you ?
SELECT * FROM my_table WHERE my_timestamp > now() - INTERVAL 5 MINUTE
If you are using this with some sort of programming language such as with PHP, then you could do something like the following:
$5_minutes_ago = date("Y-m-d H:i:s",
mktime(date("H"), (date("i") - 5), date("s"), date("m"), date("d"), date("Y"))
);
$sql = "SELECT * FROM my_timestamp > " . $5_minutes_ago;
Remember to set the date format in the first argument of date according to the one you are using in your database. Use this as a reference.
The benefit of going this route is if your database is not using an actual timestamp format that MySQL understands. You could tweak the logic to output the format in whatever way you need for your MySQL query.