Datetime between hours over a week

[black.horizons]

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!

[fenway]

Sounds like you want to group by some arbitrary time period....

[black.horizons]

is it possible to do that? if so can you provide some example of grouping by arbitrary time periods?

[fenway]

Try here.

[black.horizons]

fenway,

 

looks like that should do the job. thanks very much - one more bookmark added to my browser!