Restrict SQL results by time, MySQL

Question

Hey,

I have a table containing events which happen in my application like people logging in and people changing settings.

They have a date/time against the event in the following format:

2010-01-29 10:27:29

Is it possible to use SQL to select the events that have only happened in the last 5 mins?

So if the date/time was 2010-01-29 10:27:29 it would only select the events that happened between 10:27:29 and 10:22:29?

Cheers

Eef

Answer 1

You should provide table and column names to make it easy for us to answer your question.

You can write SQL as

SELECT *
FROM Table
WHERE DateTimeColumnName <= '2010/01/29 10:27:29'
AND  DateTimeColumnName >= '2010/01/29 10:22:29'

or you can use BETWEEN

SELECT *
FROM Table
WHERE DateTimeColumnName BETWEEN '2010/01/29 10:22:29' AND '2010/01/29 10:27:29'

Now see if there are datetime functions in MySQL to do a Date Math so just pass a single date stamp, and do the math to subtract 5 min from it and use it as the second parameter in the between clause.

Answer 2

SELECT foo FROM bar WHERE event_time > DATE_SUB(NOW(), INTERVAL 5 MINUTES)

(Not sure if it's minutes or minute)

Answer 3

WHERE my_timestamp < DATE_SUB(now(), INTERVAL 5 MINUTE)