mysql select results from 8 hour period for every day

Hey all. I have a table of user activity that goes back a couple of years. I need to select out all the users who did searches each day for more than 8 hours. Can someone give me any advice on how to do t开发者_开发技巧his with mysql? The table has userid query and search_date(datetime)

I can group by day for the user activkty, but I really need all the users who were searching for longer than the 8 hour time period.

I'm guessing you mean "find all the users who have searched twice on one day, more than 8 hours apart". I think this might work:

SELECT DISTINCT sr1.UserID, Date(sr1.Search_Date) AS Search_Date 
FROM SearchRecord sr1
JOIN SearchRecord sr2 
    ON sr1.UserID = sr2.UserID 
    AND Date(sr1.Search_Date) = Date(sr2.Search_Date) 
    AND sr1.Search_Date > (sr2.Search_Date + INTERVAL 8 HOURS)

This is assuming I properly understood your question and my MySQL syntax is correct (I use mostly SQL Server these days).

Do you need to do this just once? As in, is it throw away? If it is, doing something slow-to-run but fast-to-make might be your best bet.

I'm willing to bet some sql-guru can come up with a way to do this in a single query giving you exactly what you want.

If that doesn't happen, I'd throw together a (php/java/python/assembler/pick your poison) script/application that just reads in the data, filters it, and spits out your results. Quick and dirty, gets the job done. If you find it takes too long, then start looking at ways to speed up either the script or going back to doing it in one query.

SQL is great for a lot of things, but sometimes it's better to just do it in software. :-)