Asked
Updated
Viewed
56.5k times

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?

add a comment
1

6 Answers

  • Votes
  • Oldest
  • Latest
Answered
Updated

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)
add a comment
1
Answered
NOW() - 300

doesn't to it for you ? 🤔

add a comment
0
Answered

no i didnt get any results using now() -x number

add a comment
0
Answered

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 ?

add a comment
0
Answered
SELECT * FROM my_table WHERE my_timestamp > now() - INTERVAL 5 MINUTE
add a comment
0
Answered
Updated

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.

add a comment
0