Counting Number of Records Hour by Hour Between Two Dates in Oracle

Counting number of records hour by hour between two dates in oracle

Note the usage of trunc expression with date values. You can omit the alter session if you are not running the query in sql*plus.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT
trunc(created,'HH'),
count(*)
FROM
test_table
WHERE
created > trunc(SYSDATE -2)
group by trunc(created,'HH');

TRUNC(CREATED,'HH') COUNT(*)
------------------- ----------
2012-05-21 09:00:00 748
2012-05-21 16:00:00 24
2012-05-21 17:00:00 12
2012-05-21 22:00:00 737
2012-05-21 23:00:00 182
2012-05-22 20:00:00 16
2012-05-22 21:00:00 293
2012-05-22 22:00:00 610

8 ROWS selected.

Oracle Count number of records each hour

When you use aggregation anything in the select and order by clauses must match what's in the group by clause:

SELECT trunc(ASD,'hh'), Count(ASD) Num_CR
From DB_Name.Table_Name fcr
Where trunc(fcr.ASD) > to_date('31-DEC-2014')
And trunc(fcr.ASD) < to_date('31-JAN-2015')
And fcr.Status_Code = 'C'
Group By trunc(ASD,'hh')
Order By trunc(ASD,'hh');

When applied to a date, trunc will truncate to the day. To truncate to a different level, specify the format of the element you'd like to truncate to as the second argument (e.g. 'hh' will truncate to the hour; 'mm' will truncate to the month).

Oracle query, get count of records by hour

Assuming that your column is always in the format 2021-08-08 00:00:52:63 then group on the substring up to the 13th character:

SELECT SUBSTR(reqts, 1, 13) AS date_hr,
count(*)
FROM idcreqresplog
WHERE logdate > trunc(SYSDATE -2)
AND logtypeid in (2,4)
GROUP BY
SUBSTR(reqts, 1, 13);

If you do want to convert to a date then, from Oracle 12.2, you can use TO_TIMESTAMP(string_value DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD HH24:MI:SS:FF'):

SELECT TRUNC(
TO_TIMESTAMP(
reqts DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD HH24:MI:SS:FF'
),
'HH'
) AS date_hr,
COUNT(*)
FROM idcreqresplog
WHERE logdate > trunc(SYSDATE -2)
AND logtypeid in (2,4)
GROUP BY
TRUNC(
TO_TIMESTAMP(
reqts DEFAULT NULL ON CONVERSION ERROR,
'YYYY-MM-DD HH24:MI:SS:FF'
),
'HH'
)

db<>fiddle here

get count data between each hour in oracle

Try this:

SELECT  sum (record),TRUNC (start_time, 'hh')
FROM your_table
GROUP BY TRUNC (start_time, 'hh');

This results:

COUNT(*)         TRUNC(START_TIME,'HH')
160 10/5/2014 11:00:00 AM
100 10/5/2014 10:00:00 AM

This count rows grouping by hour. I hope this helps

oracle query between two dates with hours

A DATE has both date and time elements. To add hours to date, you just need to do some mathematics.

For example,

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT SYSDATE, SYSDATE + 1/24 FROM dual;

SYSDATE SYSDATE+1/24
------------------- -------------------
2015-08-14 15:02:59 2015-08-14 16:02:59

Adds 1 hour to sysdate. So, if you have a column of date data type, just add number of hours to be added divided by 24 because you add number of days to a date. So, you just need to first convert the number of hours into date as hours/24.

Oracle SQL query to count number of time record in between two dates

If you are asking for a count of the number of entries in a table between two timestamps then you can use this:

 SELECT COUNT( time_stamp ) AS number_of_timestamps
FROM table_name
WHERE time_stamp BETWEEN TO_DATE('2013/11/14 07:00', 'yyyy/mm/dd hh24:mi') AND TO_DATE('2013/11/16 15:00', 'yyyy/mm/dd hh24:mi');

Adjust time stamps and formatting masks as appropriate.

SQLFIDDLE

If you are looking for a purely arithmetic count of the number of times a period of hours occurs (i.e. 13:00 - 14:00) between two timestamps then you can create a function to do this:

CREATE FUNCTION count_Periods_Between (
start_time DATE,
end_time DATE,
lower_bound NUMBER,
upper_bound NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN FLOOR( end_time - start_time ) + CASE WHEN start_time - lower_bound/24 <= TRUNC( start_time ) AND end_time - upper_bound/24 >= TRUNC( end_time ) THEN 1 ELSE 0 END;
END count_Periods_Between;
/

And then just us it in a query:

SELECT  count_Periods_Between(TO_DATE( '2013/11/14 07:00', 'yyyy/mm/dd hh24:mi' ),
TO_DATE( '2013/11/16 15:00', 'yyyy/mm/dd hh24:mi' ),
13,
14
),
count_Periods_Between(TO_DATE( '2013/11/14 07:00', 'yyyy/mm/dd hh24:mi' ),
TO_DATE( '2013/11/16 15:00', 'yyyy/mm/dd hh24:mi' ),
6,
14
),
count_Periods_Between(TO_DATE( '2013/11/14 07:00', 'yyyy/mm/dd hh24:mi' ),
TO_DATE( '2013/11/16 15:00', 'yyyy/mm/dd hh24:mi' ),
7,
15
),
count_Periods_Between(TO_DATE( '2013/11/14 07:00', 'yyyy/mm/dd hh24:mi' ),
TO_DATE( '2013/11/16 15:00', 'yyyy/mm/dd hh24:mi' ),
14,
16
)
FROM DUAL;

SQLFIDDLE

get count of records in every hour in the last 24 hour

The following might be what you need. It seems to work when I run it against the all_objects view.

WITH date_range
AS (SELECT TRUNC(sysdate - (rownum/24),'HH24') as the_hour
FROM dual
CONNECT BY ROWNUM <= 1000),
the_data
AS (SELECT TRUNC(created, 'HH24') as cr_ddl, count(*) as num_obj
FROM all_objects
GROUP BY TRUNC(created, 'HH24'))
SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(num_obj,0)
FROM date_range dr LEFT OUTER JOIN the_data ao
ON ao.cr_ddl = dr.the_hour
ORDER BY dr.the_hour DESC

The 'date_range' generates a record for each hour over the past 24.

The 'the_data' does a count of the number of records in your target table based on the date truncated to the hour.

The main query then outer joins the two of them showing the date and the count from the sub-query.

I prefer both parts of the query in their own CTE because it makes the actual query very obvious and 'clean'.

In terms of your query you want this;

WITH date_range
AS (SELECT TRUNC(sysdate - (rownum/24),'HH24') as the_hour
FROM dual
CONNECT BY ROWNUM <= 24),
the_data
AS (SELECT TRUNC(systemdate, 'HH24') as log_date, count(*) as num_obj
FROM transactionlog
GROUP BY TRUNC(systemdate, 'HH24'))
SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(trans_log.num_obj,0)
FROM date_range dr LEFT OUTER JOIN the_data trans_log
ON trans_log.log_date = dr.the_hour
ORDER BY dr.the_hour DESC


Related Topics



Leave a reply



Submit