vb.net sql to retrive data in time intervals

[bensonsearch]

Hi All,

I was trying to get my head around something and cant, I want to make a report for a client that is something like this:

sql queries the log table for all records from selected date
sql groups then into 5 min intervals
count the number in each group
plot onto graph

my question is can sql extract based on time interval? if not how would i do this?

thanx in advance

[jmcilhinney]

This question really has nothing to do with VB.NET. SQL is SQL, regardless of the programming language used to write the application that passes it to the database. SQL questions belong in the Database Development forum. I have asked the mods to move this thread.

[si_the_geek]

Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB, it is certainly not specific to VB)

(thanks for letting us know jmcilhinney )

[smendoza]

What rdbms are you using? MSSQL, MySQL, Oracle?

[Shaggy Hiker]

The answer is: Possibly.

As long as the field that you are searching has time as well as date, then you could certainly query records based on 5 minute intervals. Since you mention .NET, I would guess that you are using ADO.NET for the query. If that is the case, it would probably be easiest to figure the intervals in code, such that the query has a where clause that looks something like this:

WHERE <time field> >= '" & startTime & "' AND <time field> < '" & endTime & "'"

In that example, you would replace <time field> with whichever field holds the information. However, how you fill the startTime and endTime would require a bit of thought. Technically, you could start at 12:00:00 of whichever day, and add five minutes to create the endTime. After every query, you could move the endTime to the startTime, and add 5 minutes to get the next endTime. If that was all you were doing, it would be fast and reasonable, though not totally efficient, since the times would always be the same for every day. Therefore, an alternative would be to pre-calculate a list of times at five minute intervals throughout the day. For every query, the startTime would be index N, and the endTime would be index N+1, which would be ever so slightly faster than calculating the times on the fly, because you wouldn't be doing the calculation each time. Of course, this would also have the advantage that you could add, remove, or alter the times in this list if you wanted diferent segments.

The other alternative would be to select ALL the records for the given day into a datatable, then divide them into chunks by using either LINQ, or the .Select method of the datatable. This could be the most versatile solution, and might even prove to be the most efficient, since there would be only a single round trip to the database required, with all the rest of the work done in code.

Of course, if the date field in the database doesn't have the time, then there isn't a solution possible, but I would hope that you already know that.