Menu
SELECT col1, col2, col3 FROM table WHERE DATE_ADD(last_seen, INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM ( SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records.
MySQL TIMESTAMP() FunctionThe TIMESTAMP() function returns a datetime value based on a date or datetime value. Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.
To select multiple values, you can use where clause with OR and IN operator.
The most efficient way would be to compare your timestamp (and only timestamp, without using any functions on it) to an expression that can be calculated as constant for every row, this way mysql can use index defined on your timestamp column.
SELECT * FROM myTable
WHERE last_seen >= NOW() - INTERVAL 10 MINUTE
You can always try EXPLAIN SELECT ... to see if an index can be used to find rows satisfying your WHERE condition without the need to check every row in the table.
Your question asks for records within 10 minutes of the current timestamp, but I will take that to mean no more than ten minutes in the past (not the future):
SELECT col1, col2, col3
FROM table
WHERE DATE_ADD(last_seen, INTERVAL 10 MINUTE) >= NOW();
This adds 10 minutes to last_seen and compares that against the current time. If the value is greater, last_seen is less than ten minutes ago.
See the documentation on DATE_ADD() for an explanation of how it is used.
In case the method didn't work for some.
The 'where' condition in answers above didn't work for me until i added the timestamp column inside date() function (suggested by @karam qubsi) :
SELECT * FROM myTable
WHERE date(last_seen) >= NOW() - INTERVAL 10 MINUTE
Only then i started getting correct results.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With