How to select range of data between latest DateTime minus 30 minutes-CodePudding

I'm trying to figure out how to select the latest DateTime value in a table and all previous data that came with it in a 30 minute window using MySQL.

eg table:

DateAndTime
---------
09:00:00
08:59:50
08:59:40
08:59:30
08:59:20
08:59:10
08:59:00
08:58:50
08:59:40
...
...
08:30:00

I am selecting max time as such:

SELECT MAX(`DateAndTime`) AS "DateAndTime" 
FROM TableA;

I have been trying the INTERVAL() function, but I can't seem to get that to return any other rows other than the max time.

What I tried:

SELECT MAX(`DateAndTime`) AS "DateAndTime" 
FROM TableA; 
AND `DateAndTime` - INTERVAL 30 MINUTE;

CodePudding user response：

We can use your query as subquery in the WHERE clause:

SELECT DateAndTime 
FROM tableA
WHERE DateAndTime >= 
  (SELECT MAX(DateAndTime) - INTERVAL 30 MINUTE 
  FROM tableA);

If we want to select further columns, we will just add them in the main query.

If we want to make sure the result will be sorted by date, we will add an ORDER BY clause:

SELECT DateAndTime 
FROM tableA
WHERE DateAndTime >= 
  (SELECT MAX(DateAndTime) - INTERVAL 30 MINUTE 
  FROM tableA)
ORDER BY DateAndTime;

CodePudding user response：

It looks like you'll need a subquery because you need an aggregate function to find the latest timestamp, then to use that value to return the rows you need. Try this:

SELECT DateAndTime
  FROM TableA a
 WHERE DATE_ADD(a.DateAndTime, INTERVAL 30 MINUTE) >=
       (SELECT MAX(DateAndTime) FROM TableA)