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)
SELECT * FROM my_table WHERE my_timestamp > 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 ?
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.
As you can see from the answers here there is more than one way to select rows from the database that are less than 5 minutes old.
You can also use the CURRENT_TIMESTAMP function in MySQL to get the current timestamp, and then compare it to the timestamp of the rows you want to select in your WHERE clause. For example:
SELECT * FROM table_name WHERE timestamp_column >= CURRENT_TIMESTAMP - INTERVAL 5 MINUTE;
This will select all rows from the table_name table where the value in the timestamp_column is less than 5 minutes old. MySQL CURRENT_TIMESTAMP is basically just a synonym of NOW.
You can also use the TIMESTAMPADD function to add a specific number of minutes to the current timestamp, and then use that result in your WHERE clause to select rows that are less than a certain number of minutes old:
SELECT * FROM table_name WHERE timestamp_column >= TIMESTAMPADD(MINUTE, -5, CURRENT_TIMESTAMP);
This will also select all rows from the table_name table where the value in the timestamp_column is less than 5 minutes old.