Counting results in periods of time in the day

Suppose I have a table like

CREATE TABLE associacao
(
  id bigserial NOT NULL,
  idusuario character varying(50),
  idunit character varying(50),
  dataassociacao timestamp with time zone,
  codigo bigint NOT NULL DEFAULT 0,
  CONSTRAINT associacao_pkey PRIMARY KEY (id)
)

with data like

id | idusuario | idunit | dataassociacao               | codigo
1  | "100000"  | "200"  | "2011-08-25 10开发者_JS百科:20:25.123-03" |   3
2  | "100000"  | "300"  | "2011-08-25 10:20:25.123-03" |   3
3  | "400000"  | "500"  | "2011-08-25 05:20:26.123-03" |   3
4  | "400000"  | "600"  | "2011-08-25 05:20:26.123-03" |   3
5  | "700000"  | "800"  | "2011-08-25 16:20:26.123-03" |   3
6  | "700000"  | "900"  | "2011-08-25 16:20:26.123-03" |   3
7  | "1000000" | "1100" | "2011-08-25 21:20:26.123-03" |   3
8  | "1200000" | "1300" | "2011-08-24 22:20:23.123-03" |   2
9  | "1200000" | "1300" | "2011-08-24 22:20:26.123-03" |   3

I want a SQL Statement that divides the day into 3 shifts (22:00:00.001 through 06:00:00.000, 06:00:00.001 through 14:00:00.000 and 14:00:00.001 through 22:00:00.000) and count how many distinct idusuario's each part has.

so far I have reached the following code:

SELECT 
    CASE  
       WHEN DATE_PART('hour', dataassociacao) BETWEEN 6 AND 14 THEN 1 
       WHEN DATE_PART('hour', dataassociacao) BETWEEN 14 AND 22 THEN 2 
       WHEN DATE_PART('hour', dataassociacao) BETWEEN 22 AND 24 THEN 3 
       WHEN DATE_PART('hour', dataassociacao) BETWEEN 0 AND 6 THEN 3
    END AS data, COUNT(distinct idusuario)
FROM associacao
WHERE codigo = 3
GROUP BY data
ORDER BY data;

which gives me the following table (with the above example)

data | count(idusuario)
 1   |       1
 2   |       3
 3   |       1

my problems are:

1. if I wanted a count by day, I wouldnt get the shifts counted correctly, like, 3rd shift (the night shift) would get counted as if they were working from 00:00:00.001 through 06:00:00.000 and then again from 22:00:00.001 through 00:00:00.000 in the same day, and not from 22:00:00.001 the day before through 06:00:00.000 the current day
2. the date is only comparing hour, this way, every record from 22:00:00.000 through 22:59:59.999 are counting towards 2nd shift, not 3rd shift which is the correct.

Any toughts??

thanks in advance.

---

I think the following is along the lines of what you want...

First, you're going to want a Calendar File, if you don't have one already.

Second, go ahead and define a Shift table. This would probably be important for tracking in any case, and is crucial here. Quick and dirty definition:

CREATE TABLE Shift (Shift INTEGER NOT NULL, 
                    Start_Offset SMALLINT NOT NULL,
                    End_Offset SMALLINT NOT NULL,
                    CONSTRAINT Shift_PK PRIMARY KEY(Shift))

Data to insert:

INSERT INTO Shift VALUES(1, 6, 14), (2, 14, 22), (3, 22, 30)

Then you should be able to run this query:

SELECT a.Calendar_Date, b.Shift, COUNT(DISTINCT c.idusuario)
FROM calendar as a
CROSS JOIN Shift as b
JOIN associacao as c
ON c.dataassociacao >= a.Calendar_Date + ((INTERVAL '1 hours') * b.Start_Offset)
AND c.dataassociacao < a.Calendar_Date + ((INTERVAL '1 hours') * b.End_Offset)
GROUP BY a.Calendar_Date, b.Shift
ORDER BY a.Calendar_Date, b.Shift

This results in:

Calendar_Date    Shift    Count
=======================================
2011-08-24       3        2
2011-08-25       1        1
2011-08-25       2        2 

(Please note: I performed all build/test work on DB2, which doesn't have the interval functions. From looking at the documentation and associated information online, the given query should be equivalent to what I wrote, but I cannot test on an instance of PostgreSQL).