CODEWITHSUNDEEP
sqloracle-sqldeveloper
user2562780
user2562780

Reputation: 13

I want to group by hour,min,day. how to do this?

I have a table:

CREATE TABLE SYSTEM.DATA
(
    USER VARCHAR2(20 BYTE),
    TIME  DATE,
);

INSERT INTO DATA ("USER", "TIME") VALUES ('A', '2013/3/24 AM 04:00:45');
INSERT INTO DATA ("USER", "TIME") VALUES ('B', '2013/03/24 PM 03:51:18');
INSERT INTO DATA ("USER", "TIME") VALUES ('C', '2013/03/24 PM 03:57:49');
INSERT INTO DATA ("USER", "TIME") VALUES ('D', '2013/03/25 AM 10:05:30');
INSERT INTO DATA ("USER", "TIME") VALUES ('E', '2013/03/25 AM 10:11:30');

How do I get the number of per day(being with today AM7:30,end with tomorrow AM7:29)?like this

DATE   | COUNT
03/23  |     1   ~~~THIS IS 'A', '2013/3/24 AM 04:00:45'
03/24  |     2
03/25  |     2

Upvotes: 1

Views: 60

Answers (2)

Michael
Michael

Reputation: 658

This is the solution for MS Sql Server. (I am not familiar with Oracle, but I guess that something similar is possible there as well.) @date_begin and @date_end are the parameters that you can use for the interval of dates for which you want to get the results. This solution is different from what Gordon Linoff suggested in that it will return zeroes for dates for which there are no items in the data table, whereas his query will return only dates with positive values.

    with dates (date_item) as
    (
        select dateadd(minute,450,cast(@date_begin as datetime)) as date_item
        union all
        select dateadd(dd,1,d.date_item) as date_item from dates d where d.date_item<@date_end
    )
    select
        dateadd(day, 0, datediff(day, 0, dates.date_item)),
        sum(case when data.[time]>=dates.date_item and data.[time]<dateadd(day,1,dates.date_item) then 1 else 0 end)
    from dates
    left outer join data on 1=1
    group by
        dates.date_item;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Subtract 7.5 hours from "time" and use that for aggregation:

select to_char("time" - 7.5/24, 'YYYY-MM-DD') as thedate, count(*)
from "data"
group by to_char("time" - 7.5/24, 'YYYY-MM-DD')
order by 1

Upvotes: 2

Related Questions