Select Data Grouped By Time Over Midnight

I have a table like: ID TIMEVALUE ----- ------------- 1 06.07.15 06:43:01,000000000 2 06.07.15 12:17:01,000000000 3 06.07.15 18:21:01,000000000 4

Solution 1:

EDIT: It is tested now: SQL Fiddle

The key is simply to adjust the group by so that anything before 6am gets grouped with the previous day. After that, the counts are pretty straight-forward.

SELECT TO_CHAR(CASEWHENEXTRACT(HOURFROM timevalue) <6THEN timevalue -1ELSE timevalue
                    END, 'YYYY\MM\DD') ASday, 
       COUNT(*) AS overall, 
       SUM(CASEWHENEXTRACT(HOURFROM timevalue) >=6ANDEXTRACT(HOURFROM timevalue) <14THEN1ELSE0END) AS morning,
       SUM(CASEWHENEXTRACT(HOURFROM timevalue) >=14ANDEXTRACT(HOURFROM timevalue) <23THEN1ELSE0END) AS daytime,
       SUM(CASEWHENEXTRACT(HOURFROM timevalue) <6OREXTRACT(HOURFROM timevalue) >=23THEN1ELSE0END) AS evening
FROM my_table
WHERE timevalue >= TO_TIMESTAMP('05.07.2015','DD.MM.YYYY')
GROUPBY TO_CHAR(CASEWHENEXTRACT(HOURFROM timevalue) <6THEN timevalue -1ELSE timevalue
                    END, 'YYYY\MM\DD');

Solution 2:

Substract 1 day from timevalue for times lower than '06:00' at first and then:

select TO_CHAR(day, 'YYYY\MM\DD') day, COUNT(ID) cnt, 
    SUM(casewhen'23'< tvh or  tvh <='06'THEN1ELSE0END) as midnight,
    SUM(casewhen'06'< tvh and tvh <='14'THEN1ELSE0END) as daytime,
    SUM(casewhen'14'< tvh and tvh <='23'THEN1ELSE0END) as evening
  FROM (
    select id, to_char(TIMEVALUE, 'HH24') tvh,
        trunc(casewhen (to_char(timevalue, 'hh24') <='06') 
                   then timevalue -interval'1'dayelse timevalue end) dayfrom t1
    )
  GROUPBYday

Solution 3:

Maybe you can do it like this (with some reformatting or PIVOT):

WITH spans AS 
    (SELECTTIMESTAMP'2015-01-01 00:00:00'+ LEVEL *INTERVAL'1'HOURAS start_time
    FROM dual
    CONNECTBYTIMESTAMP'2015-01-01 00:00:00'+ LEVEL *INTERVAL'1'HOUR<LOCALTIMESTAMP),
t AS 
    (SELECT start_time, lead(start_time, 1) OVER (ORDERBY start_time) AS end_time, ROWNUM AS N
    FROM spans
    WHEREEXTRACT(HOURFROM start_time) IN (6,14,23))
SELECT N, start_time, end_time, COUNT(*) AS ID_COUNT,
    DECODE(EXTRACT(HOURFROM start_time), 6,'morning', 14,'daytime', 23,'evening') AS daytime
FROM t
    JOIN YOUR_TABLE WHERE TIMEVALUE BETWEEN start_time AND end_time
GROUPBY N;

Of course, the initial time value ('2015-01-01 00:00:00' in my example) has to be lower than the least date in your table.