query mysql database with time

Question

I want to query a database in such a manner where all rows which comes in the given time are fetched. My DB table has a Date column which holds data like this

2012-04-23 08:39:10
2012-04-23 08:41:37
2012-04-23 13:49:56

i want to get all rows that contain the time like 5 min before. Actually what i am trying here is to show all users that were online 5 min before. These dates and times are created with their last activity. Any help will be very much appreciated.

i was trying this Date > SUBDATE(NOW(), INTERVAL '5' MINUTE)) but obviously it didn't worked...:( — Prashank
– Prashank, Commented Apr 23, 2012 at 20:17
what was the error or problem when you try this, because nearly all all solutions provided here even yours will gave same result, all of them are correct. SUBDATE(NOW(), INTERVAL '5' MINUTE) , DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE), subtime(now(),'00:05:00') — Nesim Razon
– Nesim Razon, Commented Apr 23, 2012 at 20:24
@Prashank: that SQL will give you all the records in the table that fall between now and 5 minutes ago. It sounds like what you wanting are only the records from 5 minutes ago. For that you will need to use an = in your SQL or try using BETWEEN. — jason
– jason, Commented Apr 23, 2012 at 20:28
It didn't gave any error it just didn't filtered the result which come within 5 min. It just gave all records of the table — Prashank
– Prashank, Commented Apr 23, 2012 at 20:31
nah i need all rows which comes within 5 min. that part is okay — Prashank
– Prashank, Commented Apr 23, 2012 at 20:36

Gustavo Vargas · Accepted Answer · 2012-04-23 20:14:33Z

3

This should work for you

select * from tablename where ((datecol > subtime(now(),'00:05:00')))

answered Apr 23, 2012 at 20:14

Gustavo Vargas

2,6272 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nesim Razon · Accepted Answer · 2012-04-23 20:19:09Z

1

SELECT *
FROM `TABLE_NAME`
WHERE `DATECOLUMN` > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE)

answered Apr 23, 2012 at 20:19

Nesim Razon

9,8043 gold badges38 silver badges48 bronze badges

Comments

jason · Accepted Answer · 2012-04-23 20:23:52Z

1

Something like the following should do the trick:

SELECT * 
FROM <table> 
WHERE 
  <date column> 
    BETWEEN 
    DATE_SUB(NOW(), INTERVAL 5 MINUTE) 
    AND 
    DATE_SUB(NOW(), INTERVAL 6 MINUTE)
;

edited Apr 23, 2012 at 20:23

answered Apr 23, 2012 at 20:16

jason

1,2551 gold badge9 silver badges26 bronze badges

Collectives™ on Stack Overflow

query mysql database with time

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related