Count Rows Per Hour in SQL Server with Full Date-Time Value as Result

  • Home
  • Languages
  • SQL
  • Count Rows Per Hour in SQL Server with Full Date-Time Value as Result

Count rows per hour in SQL Server with full date-time value as result

You actually need to round the TimeStamp to the hour. In SQL Server, this is a bit ugly, but easy to do:

SELECT dateadd(hour, datediff(hour, 0, TimeStamp), 0) as TimeStampHour, Count(*)
FROM [TEST].[dbo].[data]
GROUP BY dateadd(hour, datediff(hour, 0, TimeStamp), 0)
ORDER BY dateadd(hour, datediff(hour, 0, TimeStamp), 0);

T-SQL Get Rows Per Hour Regardless of Day

You can use DATEPART:

SELECT DATEPART(HOUR, [datetime]), COUNT(*)
FROM mytable
GROUP BY DATEPART(HOUR, [datetime])

The function returns an integer, essentially extracting the hour value from the DATETIME field.

SQL Server query for count per hour

Try this one: 

SELECT UserID, COUNT(event1) as EventsPerHour, DATEPART(HH, timestamp) as Hour
FROM [TABLE]
GROUP BY UserID, DATEPART(HH, timestamp)
HAVING COUNT(event1) >= 10

EDIT
If your table contains data for more than 24 hours you probably want to specify the date you want to filter:

Only data from today:

SELECT UserID, COUNT(event1), DATEPART(HH, datetime)
FROM [TABLE]
WHERE DATEPART(HH, datetime) = DATEPART(DAY, getdate())
GROUP BY UserID, DATEPART(HH, datetime)
HAVING COUNT(event1) >= 10

Only data from the last 24 hours:

SELECT UserID, COUNT(event1), DATEPART(HH, datetime)
FROM [TABLE]
WHERE datetime between GETDATE() and DATEADD(HH, -24, GETDATE())
GROUP BY UserID, DATEPART(HH, datetime)
HAVING COUNT(event1) >= 10

Number of rows per hour starting at a specific time

Given a specific starting time, you can get hour blocks by finding the number of minutes since your start time, and dividing by 60, then adding this number of hours back to the start time e.g.

DECLARE @StartTime DATETIME2(0) = '20160606 09:30';
WITH DummyData (mydatetime) AS
(   SELECT  TOP 200 DATEADD(MINUTE, ROW_NUMBER() OVER(ORDER BY [object_id]) - 1, @StartTime)
    FROM sys.all_objects
)
SELECT  HoursSinceStart = FLOOR(DATEDIFF(MINUTE, @StartTime, mydatetime) / 60.0),
        Display = DATEADD(HOUR, FLOOR(DATEDIFF(MINUTE, @StartTime, mydatetime) / 60.0), @StartTime),
        Records = COUNT(*)
FROM    DummyData
WHERE   myDateTime >= @StartTime
GROUP BY FLOOR(DATEDIFF(MINUTE, @StartTime, mydatetime) / 60.0)
ORDER BY Display;

Which gives:

HoursSinceStart     Display                 Records
0                   2016-06-06 09:30:00     60
1                   2016-06-06 10:30:00     40
2                   2016-06-06 11:30:00     60
3                   2016-06-06 12:30:00     20

I have left the HoursSinceStart column in, to hopefully assist in deconstructing the logic contained in the Display column

The problem with this method is that it will only give you results for blocks that exist, if you also need those that don't you will need to generate all time blocks using a numbers table, then left join to your data:

You can quickly generate a series of numbers using this:

DECLARE @StartTime DATETIME2(0) = '20160606 09:30';

-- GENERATE 10 ROWS
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),

-- CROSS JOIN THE 10 ROWS TO GET 100 ROWS
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),

--CROSS JOIN THE 100 ROWS TO GET 10,000 ROWS
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),

--APPLY ROW_NUMBER TO GET A SET OF NUMBERS FROM 0 - 99,999
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) - 1 FROM N3)

SELECT *, 
        TimeStart = DATEADD(HOUR, N, @StartTime),
        TimeEnd = DATEADD(HOUR, N + 1, @StartTime)
FROM Numbers;

Which gives something like:

N   TimeStart               TimeEnd
--------------------------------------------------
0   2016-06-06 09:30:00     2016-06-06 10:30:00
1   2016-06-06 10:30:00     2016-06-06 11:30:00
2   2016-06-06 11:30:00     2016-06-06 12:30:00
3   2016-06-06 12:30:00     2016-06-06 13:30:00
4   2016-06-06 13:30:00     2016-06-06 14:30:00
5   2016-06-06 14:30:00     2016-06-06 15:30:00
6   2016-06-06 15:30:00     2016-06-06 16:30:00
7   2016-06-06 16:30:00     2016-06-06 17:30:00

Then you can left join your data to this (you will probably need an end time too);

DECLARE @StartTime DATETIME2(0) = '20160606 09:30',
        @EndTime DATETIME2(0) = '20160606 15:30';

WITH DummyData (mydatetime) AS
(   SELECT  TOP 200 DATEADD(MINUTE, ROW_NUMBER() OVER(ORDER BY [object_id]) - 1, @StartTime)
    FROM sys.all_objects
),
-- GENERATE NUMBERS
N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) - 1 FROM N3),

TimePeriods AS 
(   SELECT  TimeStart = DATEADD(HOUR, N, @StartTime),
            TimeEnd = DATEADD(HOUR, N + 1, @StartTime)
    FROM    Numbers
    WHERE   DATEADD(HOUR, N, @StartTime) < @EndTime
)

SELECT  tp.TimeStart, tp.TimeEnd, Records = COUNT(dd.myDateTime)
FROM    TimePeriods AS tp
        LEFT JOIN DummyData AS dd
            ON dd.mydatetime >= tp.TimeStart
            AND dd.mydatetime < tp.TimeEnd
GROUP BY tp.TimeStart, tp.TimeEnd
ORDER BY tp.TimeStart;

Which will return 0 where there are no records:

TimeStart               TimeEnd                 Records
---------------------------------------------------------
2016-06-06 09:30:00     2016-06-06 10:30:00     60
2016-06-06 10:30:00     2016-06-06 11:30:00     60
2016-06-06 11:30:00     2016-06-06 12:30:00     60
2016-06-06 12:30:00     2016-06-06 13:30:00     20
2016-06-06 13:30:00     2016-06-06 14:30:00     0
2016-06-06 14:30:00     2016-06-06 15:30:00     0

How to get a count of records by minute using a datetime column

In Teradata 16.20 this would be a simple task using the new time series aggregation

SELECT 
   customer
  ,Cast(Begin($Td_TimeCode_Range) AS VARCHAR(16))
  ,Count(*)
FROM table_1
WHERE customer = 'A'
  AND time_start BETWEEN TIMESTAMP'2020-01-20 00:00:00' 
                     AND Prior(TIMESTAMP'2020-01-20 00:00:00' + INTERVAL '1' DAY)
GROUP BY TIME(Minutes(1) AND customer) 
         USING timecode(time_start)
         FILL (0)
;

Before you must implement it similar to Ramin Faracov answer, create a list of all minutes first and then left join to it. But I prefer doing the count before joining:

WITH all_minutes AS
 ( -- create a list of all minutes
   SELECT 
      Begin(pd) AS bucket
   FROM 
    ( -- EXPAND ON requires FROM and TRUNC materializes the FROM avoiding error
      -- "9303. EXPAND ON clause must not be specified in a query expression with no table references."
      SELECT Cast(Trunc(DATE '2020-01-20') AS TIMESTAMP(0)) AS start_date
    ) AS dt
   EXPAND ON PERIOD(start_date, start_date + INTERVAL '1' DAY) AS pd
          BY INTERVAL '1' MINUTE  
 )
SELECT customer
  ,bucket
  ,Coalesce(Cnt, 0)
FROM all_minutes
LEFT JOIN
 (
   SELECT customer
     ,time_start
      - (Extract (SECOND From time_start) * INTERVAL '1' SECOND) AS time_minute
      ,Count(*) AS Cnt
   FROM table_1
   WHERE customer = 'A'
     AND time_start BETWEEN TIMESTAMP'2020-01-20 00:00:00' 
                        AND Prior(TIMESTAMP'2020-01-20 00:00:00' + INTERVAL '1' DAY)
   GROUP BY customer, time_minute
 ) AS counts
ON counts.time_minute = bucket 
ORDER BY bucket
;

How to count records in a table based on each (per) hour in SQL Server?

We can go with Dynamic Pivot -

declare @ColString varchar(1000)=''
;with cte as(
    select 0 as X
    union all
    select x+1 as X
    from cte where X <23
)
select @ColString = @ColString + ',[Column' + cast(X as varchar) + ']' from cte
select @ColString = stuff(@ColString,1,1,'')

declare @DynamicQuery varchar(3000)=''
select @DynamicQuery = 
'select *
from (
    select [case],''Column''+cast(datepart(hh,[time]) as varchar) as [time]
    from #xyz
) src
pivot
(
    count([case]) for [Time] in ('+ @ColString + ')
) piv'

exec (@DynamicQuery)

Input data -

create table #xyz ([Case] varchar(10),[Time] time(0))

insert into #xyz
select 'D1','18:44' union all
select 'D2','19:12' union all
select 'C1','21:20' union all
select 'F2','21:05'

SQL Server Group by Count of DateTime Per Hour?

How about this? Assuming SQL Server 2008:

SELECT CAST(StartDate as date) AS ForDate,
       DATEPART(hour,StartDate) AS OnHour,
       COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

For pre-2008:

SELECT DATEADD(day,datediff(day,0,StartDate),0)   AS ForDate,
       DATEPART(hour,StartDate) AS OnHour,
       COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

This results in :

ForDate                 | OnHour | Totals
-----------------------------------------
2011-08-09 00:00:00.000     12       3

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

Related Topics

SQL Server Stored Procedure Parameters

SQL Select Rows with Only a Certain Value in Them

MySQL Wildcards * and %

Update with Case and in - Oracle

How to Restrict Null as Parameter to Stored Procedure SQL Server

Count Consecutive Duplicate Values in SQL

Postgresql Query to Detect Overlapping Time Ranges

SQL Server Triggers - Order of Execution

Stored Procedure Return Multiple Result Sets

SQL to Transpose Row Pairs to Columns in Ms Access Database

Select from One Table, Insert into Another Table Oracle SQL Query

Using Input from a Text File for Where Clause

SQL Query Logging for SQLite

Why Can't I Perform an Aggregate Function on an Expression Containing an Aggregate But I Can Do So by Creating a New Select Statement Around It

SQL Server 2008 Thousands Separator for a Column

What Should I Consider When Selecting a Data Type for My Primary Key

How to Average Time Intervals

Error: Column of Relation Does Not Exist Postgresql ,Unable to Run Insert Query


Leave a reply



Submit