black.horizons Posted December 10, 2009 Share Posted December 10, 2009 Hey guys, I'm trying to write a query, which is pretty deep but here's the explanation. I need to COUNT() the number of times an event occurs between the hours of 19:00:00 on day 1 and 07:00:00 on day 2. This is easily solved with ... SELECT COUNT(fieldname) FROM table WHERE TIME(fieldname) BETWEEN '2009-12-09 19:00:00' AND '2009-12-10 07:00:00' The problem is that I need to do this query to cover a period of a week with a query for each pair so that I can effectively do this: SELECT COUNT(fieldname) FROM table WHERE TIME(fieldname) BETWEEN '2009-12-09 19:00:00' AND '2009-12-10 07:00:00' SELECT COUNT(fieldname) FROM table WHERE TIME(fieldname) BETWEEN '2009-12-08 19:00:00' AND '2009-12-09 07:00:00' SELECT COUNT(fieldname) FROM table WHERE TIME(fieldname) BETWEEN '2009-12-07 19:00:00' AND '2009-12-08 07:00:00' SELECT COUNT(fieldname) FROM table WHERE TIME(fieldname) BETWEEN '2009-12-06 19:00:00' AND '2009-12-07 07:00:00' and aggregate the results. The only difficulty with doing it this way is that I need to run the query upto 10 times for each day (as there are other conditions which need met and must be selected) which would total 70 queries just to generate one dataset...not to mention the fact that I must run each query twice as I already generate a separate dataset for data between the hours of 07:00:00 and 19:00:00. all help greatly appreciated! Link to comment https://forums.phpfreaks.com/topic/184651-datetime-between-hours-over-a-week/ Share on other sites More sharing options...
fenway Posted December 10, 2009 Share Posted December 10, 2009 Sounds like you want to group by some arbitrary time period.... Link to comment https://forums.phpfreaks.com/topic/184651-datetime-between-hours-over-a-week/#findComment-974879 Share on other sites More sharing options...
black.horizons Posted December 10, 2009 Author Share Posted December 10, 2009 is it possible to do that? if so can you provide some example of grouping by arbitrary time periods? Link to comment https://forums.phpfreaks.com/topic/184651-datetime-between-hours-over-a-week/#findComment-974884 Share on other sites More sharing options...
fenway Posted December 10, 2009 Share Posted December 10, 2009 Try here. Link to comment https://forums.phpfreaks.com/topic/184651-datetime-between-hours-over-a-week/#findComment-974923 Share on other sites More sharing options...
black.horizons Posted December 16, 2009 Author Share Posted December 16, 2009 fenway, looks like that should do the job. thanks very much - one more bookmark added to my browser! Link to comment https://forums.phpfreaks.com/topic/184651-datetime-between-hours-over-a-week/#findComment-978539 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.