How to query events between an interval ?

[jesvh]

Hi :

I have a table containing two columns : event_name, event_time

I need to do query like this :

'List the events what happen more than n times between an interval'

for example :

List the events that happen more than 5 times in one hour.

( Note, 'in one hour' not means o'clock like 2:00 - 3:00, it might be 5:37 - 6:37, depends on events ).

Can it be done with SQL query ?

[jp140768]

How often are you going to want to run this report? What database are you using?

If the job was only to run once, could you create a trigger which wrote the specific event to a table, which could then be queried - this may work if the report was only to be run once per day.

Your trigger would look through the file, looking for an occurence of itself within the past hour, and if found would increment the count by one, if not, record the time and set the count to one.

HTH

[olivthill]

What kind of database do you have (Access, Oracle, MySQL, Sybase, ...)? The syntax for handling dates is different from one database to the other.

Your query will look like:

Select event_name
From T_events
Where event_time > start_time
and event_time < start_time_plus_one_hour

If you don't know the time for the beginning, then, you would have two queries, with the first one where you get the starting time, and the second one, where you use it as shown above. With some databases, you might be able to combine the two queries into one.

Another solution, which is the one I would opt for, would be to retrieve every event and time for a day, put them in an array, and then have a loop in C, VB, PHP, or another classical programming language, and get the results you are exepecting.

[jesvh]

How often are you going to want to run this report? What database are you using?

If the job was only to run once, could you create a trigger which wrote the specific event to a table, which could then be queried - this may work if the report was only to be run once per day.

Your trigger would look through the file, looking for an occurence of itself within the past hour, and if found would increment the count by one, if not, record the time and set the count to one.

HTH


Thank you !

It is indeed a simpler solution to active at once than query later.

But, it seems need to maintain a list of all events .......