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
Return records with counts for the last 24 hours
Try this, it will also count the hours without data:
DECLARE @t table(ID int, Date datetime)
INSERT @t values
(1,'2015-01-19 10:29:00.000'), (2,'2015-01-19 11:29:00.000'),
(3,'2015-01-19 11:29:00.000'), (4,'2015-01-19 11:29:00.000'),
(5,'2015-01-19 12:29:00.000'), (6,'2015-01-19 12:29:00.000'),
(7,'2015-01-19 12:29:00.000'), (8,'2015-01-19 12:29:00.000'),
(9,'2015-01-17 13:29:00.000'), (10,'2015-01-17 13:29:00.000'),
(11,'2015-01-17 13:29:00.000'),(12,'2015-01-17 13:29:00.000'),
(13,'2015-01-17 13:29:00.000'),(14,'2015-01-17 13:29:00.000'),
(15,'2015-01-17 14:29:00.000'),(17,'2015-01-17 15:29:00.000'),
(18,'2015-01-17 15:29:00.000'),(19,'2015-01-17 16:29:00.000'),
(20,'2015-01-17 16:29:00.000'),(21,'2015-01-15 16:29:00.000'),
(22,'2015-01-15 17:29:00.000'),(23,'2015-01-15 18:29:00.000'),
(24,'2015-01-15 18:29:00.000'),(25,'2015-01-15 18:29:00.000'),
(26,'2015-01-15 18:29:00.000'),(27,'2015-01-15 18:29:00.000'),
(28,'2015-01-15 18:29:00.000'),(29,'2015-01-15 19:29:00.000'),
(30,'2015-01-10 20:29:00.000')
DECLARE @yourdate datetime = '2015-01-19T12:30:00.000'
;WITH CTE AS
(
SELECT dateadd(hh, datediff(hh, 0, @yourdate), 0) Date
UNION ALL
SELECT dateadd(hh, -1, Date)
FROM CTE
WHERE Date + 1 > @yourdate
)
SELECT CTE.Date, count(t.id) count
FROM CTE
LEFT JOIN @t t
ON CTE.Date <= t.Date
and dateadd(hh, 1, CTE.Date) > t.Date
GROUP BY CTE.Date
ORDER BY CTE.Date DESC
Result:
Date Count
2015-01-19 12:00:00.000 4
2015-01-19 11:00:00.000 3
2015-01-19 10:00:00.000 1
2015-01-19 09:00:00.000 0
2015-01-19 08:00:00.000 0
.....
Return count for each hour in last 24 hours filling in blanks with zeros
I took the advice of @Strawberry in the comment section and decided to do this application level, which now makes a lot of sense, as it's much easier to read. I don't regret trying the SQL version, as I learned a bit.
I'm posting the solution I went with below, which is in PHP for anybody that needs something similar. If anybody has a more efficient solution than this please share in the comments.
The Query:
SELECT
CONCAT(DATE_FORMAT(created, '%Y-%m-%d %H'), ':00:00') AS saleHour,
SUM(amount) AS totalSales
FROM eventTicketSales
WHERE created BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR) AND UTC_TIMESTAMP
GROUP BY HOUR(created);
The result:
saleHour totalSales
2019-01-11 17:00:00 10000
2019-01-10 23:00:00 20000
Stored in $salesIn24Hours
The Logic:
$hours = 24 + 1;
for ($i=0; $i<$hours; $i++) {
$date = date('Y-m-d H', strtotime('-'.$i.' HOUR')).':00:00';
$key = array_search($date, array_column($salesIn24Hours, 'saleHour'));
if ($key !== FALSE) {
echo $date . ' ' . $salesIn24Hours[$key]['totalSales'] . '<br>';
} else {
echo $date . ' 0' . '<br>';
}
}
The Result:
2019-01-11 21:00:00 0
2019-01-11 20:00:00 0
2019-01-11 19:00:00 0
2019-01-11 18:00:00 0
2019-01-11 17:00:00 10000
2019-01-11 16:00:00 0
2019-01-11 15:00:00 0
2019-01-11 14:00:00 0
2019-01-11 13:00:00 0
2019-01-11 12:00:00 0
2019-01-11 11:00:00 0
2019-01-11 10:00:00 0
2019-01-11 09:00:00 0
2019-01-11 08:00:00 0
2019-01-11 07:00:00 0
2019-01-11 06:00:00 0
2019-01-11 05:00:00 0
2019-01-11 04:00:00 0
2019-01-11 03:00:00 0
2019-01-11 02:00:00 0
2019-01-11 01:00:00 0
2019-01-11 00:00:00 0
2019-01-10 23:00:00 20000
2019-01-10 22:00:00 0
2019-01-10 21:00:00 0
SQL to count records per hour and count how many hours that total record count was greater than a value
You kind of have a 2-part question here
I would like to count how many records there are per hour
You can create a query that returns tuples (RecordsPerHour,HOUR,YEAR,MONTH,DAY) as follows:
SELECT
count(*) as RecordsPerHour,
datepart(hour,convert(datetime,OrderStateDate)) as Date_HOUR,
datepart(year,convert(datetime,OrderStateDate)) as Date_YEAR,
datepart(month,convert(datetime,OrderStateDate)) as Date_MONTH,
datepart(day,convert(datetime,OrderStateDate)) as Date_DAY
FROM Reporting.dbo.Orders
GROUP BY
datepart(year,convert(datetime,OrderStateDate)),
datepart(month,convert(datetime,OrderStateDate)),
datepart(day,convert(datetime,OrderStateDate)),
datepart(hour,convert(datetime,OrderStateDate))
then count how many hours each day that the record count exceeded a given number, say 20
To do this, use the query from the first part of your question in a nested query, using a HAVING
clause to filter only hours that contain at least 20 orders.
On the outer query, group by (YEAR,MONTH,DAY) to determine the number of hours in that day with at least 20 orders:
SELECT
count(*) as HoursWithAtLeast20Orders,
Date_YEAR,
Date_MONTH,
Date_DAY
FROM
(SELECT
datepart(hour,convert(datetime,OrderStateDate)) as Date_HOUR,
datepart(year,convert(datetime,OrderStateDate)) as Date_YEAR,
datepart(month,convert(datetime,OrderStateDate)) as Date_MONTH,
datepart(day,convert(datetime,OrderStateDate)) as Date_DAY
FROM Reporting.dbo.Orders
GROUP BY
datepart(year,convert(datetime,OrderStateDate)),
datepart(month,convert(datetime,OrderStateDate)),
datepart(day,convert(datetime,OrderStateDate)),
datepart(hour,convert(datetime,OrderStateDate))
HAVING count(*) >=20) as t
GROUP BY
Date_YEAR,
Date_MONTH,
Date_DAY
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
Count of orders in last 24 by hour
Try This :
$hourlyOrders = Order::where('created_at', '>', Carbon::now()->subDays(1))
->groupBy(\DB::raw('HOUR(created_at)'))
->count();
EDIT :
$orders = DB::table('Order') // write your table name
->selectRaw('count(order_id) as total_orders')) // use your field for count
->where('created_at', '>', Carbon::now()->subDays(1))
->groupBy(\DB::raw('HOUR(created_at)'))
->get();
Get hourly result for count in the last 48 hours - MySql
If you want every hour, don't you want this:
SELECT count(*), date_format(created, '%H - %d/%m/%y') as datecreated
FROM mimesi_indexer.served_clips
where created > NOW() - INTERVAL 24 HOUR
group by date_format(created, '%H - %d/%m/%y')
order by min(created) ASC;
That is, you need to include the day as well as the hour -- and remove the minutes from datecreated
.
Related Topics
How to Change Date Format in .Net Rdlc Report
Convert a 12 Hour Format to 24 Hour Format in SQL Server
Loop Through Table Rows and Call Stored Procedure on Every Row
How to Delete Multiple Rows in Diferent Tables
Sql - Finding Students Taking 2 or More Classes
Mysql Error 1449: the User Specified as a Definer Does Not Exist
Trim Whitespaces (New Line and Tab Space) in a String in Oracle
State Wise Data of Country and City
Get Count of Records in Every Hour in the Last 24 Hour
How to Check in Sqlite Whether a Table Exists
Solve Query for Showing Top 5 Selling Products
Update Only Time from My Datetime Field in SQL
A SQL Query to Select a String Between Two Known Strings
Sql String: Counting Words Inside a String
Combining Multiple Rows in SQL Server into One
How to Check If Field Is Null or Empty in MySQL