SQL to fetch the count between tow date range

I require to fetch the count of PRODUCT_ID between 00:15:00 and 01:15:00 and subsequentially for any date range.

Example Scripts:-

My DB structure and data is as follows.

CREATE TABLE time1 (cr_date date , product_id number );

insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12345);
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12346);
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12347);
insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42345);
insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42346);
insert into time1 values (to_date ('01-JAN-2022 03:35:00', 'DD_MON-YYYY HH:MI:SS') , 42347);
insert into time1 values (to_date ('01-JAN-2022 03:40:00', 'DD_MON-YYYY HH:MI:SS') , 42348);
insert into time1 values (to_date ('01-JAN-2022 10:40:00', 'DD_MON-YYYY HH:MI:SS') , 10348);
insert into time1 values (to_date ('01-JAN-2022 10:42:00', 'DD_MON-YYYY HH:MI:SS') , 10349);
insert into time1 values (to_date ('01-JAN-2022 10:43:00', 'DD_MON-YYYY HH:MI:SS') , 11348);

COMMIT;

Output is required to be as below:-

| hours   | count |
|:------  |:------|
|00:15:00 |3|
|01:15:00 |0|
|02:15:00 |0|
|03:15:00 |0|
|04:15:00 |4|
|05:15:00 |0|
|06:15:00 |0|
|07:15:00 |0|
|08:15:00 |0|
|09:15:00 |0|
|10:15:00 |0|
|11:15:00 |3|
|..
|...
|23:15:00 |0|

Sample data you (initially) posted is pretty much useless, there's no time component involved.

In one of my tables, there's a datum column and values look like this:

SQL> SELECT id, TO_CHAR (datum, 'hh24:mi') hrs FROM obr WHERE rownum <= 10;

        ID HRS
---------- -----
     21547 08:41
     21541 08:17
     21563 09:03
     21614 10:46
     21618 11:01
     21620 11:04
     21622 11:05
     21626 11:10
     21629 11:14
     21642 13:35

10 rows selected.

SQL>

This is query which

• in fmin CTE creates 24 rows (00:15, 01:15, ... 23:15)
• join is done on datum "rounded" to previous 15-minute value (whether in the same hour, or in previous hour - depends on minutes)

So:

SQL> WITH
  2     fmin
  3     AS
  4        (    SELECT TRUNC (SYSDATE) + (LEVEL - 1) / 24 + INTERVAL '15' MINUTE c_time
  5               FROM DUAL
  6         CONNECT BY LEVEL <= 24)
  7    SELECT TO_CHAR (f.c_time, 'hh24:mi') c_time, COUNT (z.id) cnt
  8      FROM fmin f
  9           LEFT JOIN obr z
 10              ON TO_CHAR (f.c_time, 'hh24:mi') =
 11                 TO_CHAR (
 12                      TRUNC (datum, 'hh24')
 13                    + CASE
 14                         WHEN TO_NUMBER (TO_CHAR (datum, 'mi')) >= 15
 15                         THEN
 16                            INTERVAL '15' MINUTE
 17                         WHEN TO_NUMBER (TO_CHAR (datum, 'mi')) < 15
 18                         THEN
 19                            INTERVAL '-45' MINUTE
 20                      END,
 21                    'hh24:mi')
 22  GROUP BY TO_CHAR (f.c_time, 'hh24:mi')
 23  ORDER BY 1;

Result:

C_TIM        CNT
----- ----------
00:15          0
01:15          0
02:15          0
03:15          0
04:15          0
05:15          0
06:15          0
07:15          2
08:15         10
09:15          1
10:15         14
11:15          6
12:15         10
13:15         38
14:15          5
15:15          0
16:15          0
17:15          0
18:15          0
19:15          0
20:15          0
21:15          0
22:15          0
23:15          0

24 rows selected.

SQL>

You wont get count based on time as you are storing only date in CR_DATE column.

Even if you convert date to timestamp, the time will always be set to default value i.e., 00:00:00.000000 or 12:00:00.000000.

To achieve this, you have to re-create your table like below :

CREATE TABLE time1 (cr_date timestamp, product_id number );

And then, you have to insert actual timestamp into the table like the one example below.

INSERT INTO TIME1 (CR_DATE,PRODUCT_ID) VALUES (to_timestamp('01-01-22 14:32:62.258152','DD-MM-RR HH24:MI:SS.FF'),12345);

Here is the documentations for to_timestamp.

After this, you can query based on time as per your question.

SELECT TRUNC(CR_DATE,'HH'), count(product_id) 
FROM TIME1 
GROUP BY TRUNC(CR_DATE,'HH');

Here is the documentation for TRUNC() function.

The following result can be possibly obtained from above query.

TRUNC(CR_DATE,'HH')   COUNT(product_id)
------------------- ----------
2022-01-01 09:00:00        748
2022-01-01 16:00:00         24
2022-01-01 17:00:00         12
2022-01-01 22:00:00        737
2022-01-01 23:00:00        182
2022-01-01 20:00:00         16
2022-01-01 21:00:00        293
2022-01-01 22:00:00        610

If you find any issue, please comment below for further discussion.