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
Looking for a SQL Transaction Log File Viewer
How to Produce an CSV Output File from Stored Procedure in SQL Server
SQL Update Records with Row_Number()
Gather_Plan_Statistics Does Does Not Generate Basic Plan Statistics
Stored Procedure Return Multiple Result Sets
Why Use the Between Operator When We Can Do Without It
Using a Having Clause in an Update Statement
Minimizing SQL Queries Using Join with One-To-Many Relationship
Postgresql: Fill Null Values in Timeserie Query with Previous Value
Is Null VS = Null in Where Clause + SQL Server
How to Pass a Comma Separated List to a Stored Procedure
SQL 2005 Cte VS Temp Table Performance When Used in Joins of Other Tables
Executing a Dynamic SQL Statement into a Sys_Refcursor
SQL Server Format Decimal Places with Commas