CODEWITHSUNDEEP
mysqldatetime
dotancohen
dotancohen

Reputation: 31481

Once an hour results from MySQL

Consider a table such as this:

CREATE TABLE records (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    eventOccured DATETIME NOT NULL,
    eventType INT NOT NULL,
    eventDescription VARCHAR(32)
)

I would like to retrieve the eventOccured and eventType fields from the entire table, but if two or more events occurred within an hour of each other then I would want only the first of them. This would be be simple if everything from N:00 to N:59 were considered the same hour, but in this case an event at 12:15 of eventType "5" would be considered to have occurred less than an hour after an 11:45 eventType "5", and so should not be returned. Can this be done in MySQL without a stored procedure? I could write such a procedure, but I worry that it will be rather resource-intensive and I would love to learn if MySQL has such ability out of the box.

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

What you are saying is that you want all events, where there is not another event in the next 60 minutes. This query does this in what should be a mysql friendly way:

select *
from records r
where not exists (select 1
                  from records r2
                  where r2.eventOccured > r.eventOccured and
                        timestampdiff(minute, r.eventOccured, r2.eventOccured) < 60
                 )

Upvotes: 1

Related Questions