CODEWITHSUNDEEP
sql-server
Sunny Milenov
Sunny Milenov

Reputation: 22320

TS SQL - group by minute

I have a table with timestamps. What is the proper query to get the records counts for each minute for the last hour.

I.e. if now is 2:25, I want to know how many record were between 1:25 and 1:26, 1:26 and 1:27, and so on, so I have 60 results.

Upvotes: 24

Views: 41822

Answers (7)

slartidan
slartidan

Reputation: 21608

If you want to group results by minute, then you can use a formatted string. This will group by number of minutes since 1/1/1900 not minute within day.

WITH formatted AS (
    SELECT FORMAT(<your_datetime_column>, 'yyyy-MM-dd HH:mm') AS minute_text
    FROM <your_table>
)
SELECT minute_text, COUNT(*) AS number_of_rows
FROM formatted
GROUP BY minute_text
ORDER BY 1 DESC

Upvotes: 3

CuriousCarnivore
CuriousCarnivore

Reputation: 107

Here's my fixed up version of Robin's answer. I made it output the errors in the correct order and output the time as well instead of just the number which isn't super useful if you're charting this out.

WITH numbers ( num ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + num FROM numbers WHERE num < 60 )
SELECT (SELECT DATEADD(n, -num, GETDATE())) AS TimeStamp,
    (SELECT COUNT(*) AS results
    FROM ErrorLogs
    WHERE DATEPART(n, TimeStamp) = DATEPART(n, DATEADD(n, -num, GETDATE()))
    AND TimeStamp > DATEADD(hh, -1, GETDATE())) as Count
FROM numbers

Upvotes: 0

Aeren Robinson
Aeren Robinson

Reputation: 231

This is an alternative I have found useful for determining how many records are inserted or updated per minute. The nice thing about having your date format as a variable up front is that you can easily change it to analyze per hour instead. Hope this helps!

DECLARE @dateFormat as varchar(max) = 'yyyy-MM-dd HH:mm'

SELECT format(timeColumn, @dateFormat) AS minute, COUNT(*) as results
FROM yourTable
WHERE timeColumn > DATEADD(hh, -1, GETDATE())
GROUP BY  format(timeColumn, @dateFormat)
ORDER BY 1

Upvotes: 14

Tomas Aschan
Tomas Aschan

Reputation: 60674

As you edited the question, I edit my answer. If I have understood you correctly, you want to look only at the past hour - that is, a timespan from one hour before the request is made to the current time. This is how I'd do it:

SELECT
    COUNT(yourTimeStamp)
FROM yourTable
WHERE DATEADD('hh', -1, GetDate()) <= yourTimeStamp 
    AND yourTimeStamp < GetDate()
GROUP BY DATEPART('mm', yourTimeStamp)

I am not entirely sure that the syntax is exact. When coding in MSSQL, I would use the CURRENT_TIMESTAMP for the current time, MINUTE instead of DATEPART etc, but you get the idea for the solution.

Upvotes: 3

Robin Minto
Robin Minto

Reputation: 15387

This will return a count of results for each minute (where you have records) in the last hour 

SELECT DATEPART(n, time_stamp) AS minute, COUNT(*) as results
FROM table_name 
WHERE time_stamp > DATEADD(hh, -1, GETDATE())
GROUP BY DATEPART(n, time_stamp)

This may return less than 60 results, depending on the data. If you have to have 60 results, the query is slightly different. This uses a Common Table Expression to generate a list of 60 numbers and a correlated sub-query to get the results for each minute:

WITH numbers ( num ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + num FROM numbers WHERE num < 60 )
SELECT num AS minute,
    (SELECT COUNT(*) AS results
    FROM table_name
    WHERE DATEPART(n, time_stamp) = num
    AND time_stamp > DATEADD(hh, -1, GETDATE())
FROM numbers

To see the results, replace DATEADD(hh, -1, GETDATE()) with DATEADD(mi, -15, GETDATE()) and you'll get the results for the last 15 minutes and 0 for other minutes.

Upvotes: 29

p.campbell
p.campbell

Reputation: 100667

DATEPART is what you're looking for:

declare @times table
(
    someTime datetime
)

INSERT INTO @Times (sometime) values ('jan 12 2008 12:23')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:34')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:35')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:25')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:02')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:09')
INSERT INTO @Times (sometime) values ('jan 12 2008 12:35')


select DATEPART(mi,sometime) AS Minute, count(*) AS NumOccurances
from @Times
WHERE SomeTime BETWEEN @Lower AND @Upper
GROUP BY DATEPART(mi, sometime)
order by NumOccurances DESC

Result:

Minute  NumOccurances
35  2
2   1
9   1
23  1
25  1
34  1

Upvotes: 2

Paul Sonier
Paul Sonier

Reputation: 39520

SELECT COUNT (TS) from TABLE where TABLE.TS BETWEEN(starttime, endtime)

Upvotes: -1

Related Questions