0

This is my mysql table

-----   -----------   -------------------
b_id    update_time   update_time_text
-----   -----------   -------------------
A:340   1436507350    10-07-2015 11:19:10
A:340   1436507707    10-07-2015 11:25:07
A:340   1436509704    10-07-2015 11:58:24
A:340   1436507828    10-07-2015 11:27:08

And this is my query

SELECT * FROM table WHERE b_id='A:340' AND update_time < (NOW() - 120) // 120 is 2 minutes

How do i select records in the last 2 mintues since my query is returning empty records and i'm not sure if i have to take unix time(update_time) or the readable time(update_time_text)

user3721305
  • 79
  • 1
  • 11

4 Answers4

2

You can use date_sub and interval 2 minute

SELECT * FROM table WHERE b_id='A:340' AND update_time < date_sub(NOW() - interval 2 minute) // 120 is 2 minutes
Saty
  • 22,443
  • 7
  • 33
  • 51
2

as update_time is a unix timestamp field then replace your NOW() with UNIX_TIMESTAMP():

SELECT * FROM table WHERE b_id='A:340' AND update_time >= (UNIX_TIMESTAMP() - 120)

records older than 2 minutes:

SELECT * FROM table WHERE b_id='A:340' AND update_time < (UNIX_TIMESTAMP() - 120)
Wee Zel
  • 1,294
  • 9
  • 11
1

Try this query

SELECT * FROM table WHERE b_id='A:340' AND TIME_TO_SEC(update,NOW())<=120

If you want difference of time in second more detail refer this link

MySQL: how to get the difference between two timestamps in seconds

Community
  • 1
  • 1
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
0

You can try this:

SELECT * FROM table WHERE b_id='A:340' AND update_time BETWEEN DATE_SUB(NOW(), INTERVAL 2 MINUTE) AND NOW()
Vishal JAIN
  • 1,940
  • 1
  • 11
  • 14