Group By sets of values

for a report I'm trying to query events from different shifts. The shifts start on 6 am, 2 pm, and 10 pm every day, and all of the data in the table is tagged with a datetime timestamp. Previously the graveyard shift wasn't doing anything important, so a simple group by DATE(stamp) was sufficient, but now it's 24/7 and I need to break it down into shifts.

Can anyone explain to me how to use a single group by clause to combine datetime values from a range or a set of values? The d开发者_运维问答ifficulty is that each graveyard shift spans two calendar days.

I've considered populating a table with 24 hours and shift numbers, then outer joining it and group by DATE(stamp), HOUR(stamp), but that seems hackish and possibly not even working, plus it would give 24 values for each day instead of 3, which then have to be combined in a superquery or script.

MySQL-specific is perfectly ok, that's all we ever use in the reporting.

Since they are all 8-hour shifts, offset by 6 hours from starting at midnight, you turn Stamp into the start-of-shift time like this:

select
    stamp,
    adddate(date(subdate(stamp, interval 6 hour)), 
      interval ((hour(subdate(stamp, interval 6 hour))
      div 8) * 8) + 6 hour) as shift_start
from mytable;

This substracts 6 hours, then rounds the hour down to either 0 1 or 2 by using integer division, then expands it out again.

Here's the test code with some edge cases:

create table mytable (stamp datetime);
insert into mytable values ('2011-08-17 22:00:00'), ('2011-08-17 23:01:00'), 
('2011-08-18 00:02:00'), ('2011-08-18 05:59:00'), ('2011-08-18 06:00:00'),
('2011-08-18 13:59:00'), ('2011-08-18 14:00:00'), ('2011-08-18 17:59:00');

Output of above query:

+---------------------+---------------------+
| stamp               | shift_start         |
+---------------------+---------------------+
| 2011-08-17 22:00:00 | 2011-08-17 22:00:00 |
| 2011-08-17 23:01:00 | 2011-08-17 22:00:00 |
| 2011-08-18 00:02:00 | 2011-08-17 22:00:00 |
| 2011-08-18 05:59:00 | 2011-08-17 22:00:00 |
| 2011-08-18 06:00:00 | 2011-08-18 06:00:00 |
| 2011-08-18 13:59:00 | 2011-08-18 06:00:00 |
| 2011-08-18 14:00:00 | 2011-08-18 14:00:00 |
| 2011-08-18 17:59:00 | 2011-08-18 14:00:00 |
+---------------------+---------------------+

Try this:

GROUP BY DATE(DATE_ADD(Stamp,INTERVAL -6 HOUR))

That should keep all your shifts on the same day

I think you should pursue your "table with hours and shift numbers" approach. Further, I think you should consider using a calendar table i.e. a table not just covering 24 hours but the whole past, present and future of your enterprise's expected needs. This is not hackish: rather, it is a tried and tested approach. The idea is that SQL is a declarative language designed to query data in tables so a declarative, data-driven solutions make a lot of sense.