CODEWITHSUNDEEP
mysqlsql
Michael
Michael

Reputation: 2867

MySQL, return all results within X last hours

My table is composed of multiple columns, including unixtimestamp:

CREATE MyTable(
   id INT NOT NULL AUTO_INCREMENT,
   ...
   timeStart INT(11)
)ENGINE=MyISAM;

New items are inserted continually to this table.

I need to return all items with a timestamp > (MAX(startTime)-X_duration)

I am interested to write a single and optimal query to achieve it.

  1. One easy way to do it, is to write nested select, where the inner select returns the MAX(timeStart) as maxTime , than select all records greater than in where clause.

I am interested if there are better and more efficient solutions?

Upvotes: 1

Views: 124

Answers (1)

John Woo
John Woo

Reputation: 263893

SELECT  *
FROM    tableName
WHERE   timeStart > (SELECT MAX(timeStart) + INTERVAL -1 SECOND FROM tableName)

Upvotes: 2

Related Questions