CODEWITHSUNDEEP
mysqlsql
krx
krx

Reputation: 2683

MySQL Select rows where timestamp column between now and 10 minutes ago

I have a timestamp column that auto updates on insert/update.

I want to get the rows that have been updated within the last 10 minutes. 

SELECT
     *
FROM
     status
WHERE
     code='myCode'
AND
     'stamp_updated' 
     BETWEEN
     NOW()
     AND
     DATE_ADD(NOW() , INTERVAL - 10 MINUTE)
ORDER BY 
     stamp_updated DESC
LIMIT 1

Upvotes: 11

Views: 29174

Answers (3)

Gregory Patmore
Gregory Patmore

Reputation: 1164

Not sure why you're using the between construct. MySQL can use logical operators on dates, and usually significantly faster. I would use this:

select * 
  from status 
  where code='myCode' 
        and stamp_updated >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) 
  order by stamp_updated desc 
  limit 1;

Upvotes: 5

a1ex07
a1ex07

Reputation: 37364

 ... 'stamp_updated' BETWEEN NOW() - INTERVAL 10 MINUTE AND NOW()  ...

Upvotes: 7

OMG Ponies
OMG Ponies

Reputation: 332631

Use:

  SELECT *
    FROM status
   WHERE code = 'myCode'
     AND `stamp_updated` BETWEEN DATE_SUB(NOW() , INTERVAL 10 MINUTE)
                           AND NOW()
ORDER BY stamp_updated DESC
   LIMIT 1

Order in the BETWEEN operator matters - you had it backwards.

Upvotes: 25

Related Questions