How to Retrieve Records for Last 30 Minutes in Ms SQL

How to retrieve records for last 30 minutes in MS SQL?

Change this (CURRENT_TIMESTAMP-30)

To This: DateADD(mi, -30, Current_TimeStamp)

To get the current date use GetDate().

MSDN Link to DateAdd Function

MSDN Link to Get Date Function

Get data from last 30 minutes and get the latest rows

If you are wanting the latest row from the last 30 minutes for each unique "mmsi" that has one, then using a join to a subquery where you find the max timestamps first should work, like:

SELECT timestamp, a.mmsi, navstatus, rot, sog, lon, lat, cog, thead, man, mtype
FROM ais_cnb a INNER JOIN
(SELECT mmsi, MAX(timestamp) AS max_timestamp FROM ais_cnb
 WHERE timestamp > (now() - interval 30 minute)
 GROUP BY mmsi) t
ON ((timestamp = t.max_timestamp) AND (a.mmsi = t.mmsi))

Query to find the records creating in last 30 mins and then the records created in last 1 hour

Your idea is correct, but the proper syntax is this:

SELECT "last 30 minutes" as t, category_id, count(*) as cnt
FROM items
WHERE created_at >= NOW() - INTERVAL 30 MINUTE
GROUP BY category_id
UNION ALL
SELECT "last 1 hour" as t, category_id, count(*) as cnt
FROM items
WHERE
  created_at < NOW() - INTERVAL 30 MINUTE
  AND created_at >= NOW() - INTERVAL 60 MINUTE
GROUP BY category_id

...etc...

This query will count the events in the last 30 minutes, grouped by category_id, and then all events in the last hour but not in the last 30 minutes.

UNION ALL

is better than UNION on this context because UNION will return unique rows.

Edit

Based on your updated question, I think you are looking for this:

SELECT items.*
FROM items
WHERE created_at>=NOW() - INTERVAL 60 MINUTE
ORDER BY
  (created_at >= NOW() - INTERVAL 30 MINUTE) DESC,
  category_id

if you have more intervals then you can use this:

SELECT items.*
FROM items
WHERE created_at>=NOW() - INTERVAL 120 MINUTE
ORDER BY
  (created_at >= NOW() - INTERVAL 30 MINUTE) DESC,
  (created_at >= NOW() - INTERVAL 60 MINUTE) DESC,
  category_id

---

Related Topics