Get Count of Records in Every Hour in the Last 24 Hour

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



Leave a reply



Submit