How to group time by hour or by 10 minutes
SqlSql Server-2008TsqlGroup BySql Problem Overview
like when I do
SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date]
how can I specify the group period ?
MS SQL 2008
2nd Edit
I'm trying
SELECT MIN([Date]) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY (DATEPART(MINUTE, [Date]) / 10)
ORDER BY RecT
changed %10 to / 10. is it possible to make Date output without milliseconds ?
Sql Solutions
Solution 1 - Sql
finally done with
GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)
Solution 2 - Sql
Short and sweet
GROUP BY DATEDIFF(MINUTE, '2000', date_column) / 10
With heavy acknowledgements to Derek's answer, which forms the core of this one.
Practical usage
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
AS [date_truncated],
COUNT(*) AS [records_in_interval],
AVG(aa.[value]) AS [average_value]
FROM [friib].[dbo].[archive_analog] AS aa
-- WHERE aa.[date] > '1900-01-01'
GROUP BY DATEDIFF(MINUTE, '2000', aa.[date]) / 10
-- HAVING SUM(aa.[value]) > 1000
ORDER BY [date_truncated]
Details and commentary
-
The
MINUTE
and10
terms can be changed to anyDATEPART
and integer,1 respectively, to group into different time intervals.-
e.g.
10
withMINUTE
is ten minute intervals;6
withHOUR
is six hour intervals. -
If you change the interval a lot, you might benefit from declaring it as a variable.
DECLARE @interval int = 10; SELECT DATEADD(MINUTE, DATEDIFF(…) / @interval * @interval, '2000') … GROUP BY DATEDIFF(…) / @interval
-
-
Wrapping it with a
DATEADD
invocation with a multiplier will give you aDATETIME
value, which means:- Data sources over long time intervals are fine. Some other answers have collision between years.
- Including it in the
SELECT
statement will give your output a single column with the truncated timestamp.
-
The truncating integer division (a
FLOOR
shortcut) makes the date output shown in aSELECT
the beginning of each time interval. If you want the middle or end of the interval, you can tweak the division in the second term ofDATEADD
with the bold part below:- End of interval:
…) / 10 * 10
+ 10
, '2000')
, credit to Daniel Elkington. - Middle of interval:
…) / 10 * 10
+ (10 / 2.0)
, '2000')
.
- End of interval:
Trivia
'2000'
is an "anchor date" around which SQL will perform the date math. Most sample code uses 0
for the anchor, but JereonH discovered that you encounter an integer overflow when grouping more-recent dates by seconds or milliseconds.2
If your data spans centuries,3 using a single anchor date in the GROUP BY
for seconds or milliseconds will still encounter the overflow. For those queries, you can ask each row to anchor the binning comparison to its own date's midnight:
-
Use
DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date]), 0)
instead of'2000'
wherever it appears above. Your query will be totally unreadable, but it will work. -
An alternative might be
CONVERT(DATETIME, CONVERT(DATE, aa.[date]))
as the replacement.
1 If you want all :00
timestamps to be eligible for binning, use an integer that your DATEPART
's maximum can evenly divide into.4 As a counterexample, grouping results into 13-minute or 37-hour bins will skip some :00
s, but [it should still work fine][nikow].
2 The math says 232 ≈ 4.29E+9. This means for a DATEPART
of SECOND
, you get 4.3 billion seconds on either side, which works out to "anchor date ± 136 years." Similarly, 232 milliseconds is ≈ 49.7 days.
3 If your data actually spans centuries or millenia and is still accurate to the second or millisecond… congratulations! Whatever you're doing, keep doing it.
4 If you ever wondered why our clocks have a 12 at the top, reflect on how [5 is the only integer][divisors] from 6 (half of 12) or below that is not a factor of 12. Then note that 5 × 12 = 60. You have lots of choices for bin sizes with hours, minutes, and seconds.
[nikow]: https://stackoverflow.com/a/66742564/241211 "Grouping minutes into bins of 90" [divisors]: https://docs.google.com/spreadsheets/d/1iYPs9-9YWyOGVQ26vyQHKJMg6Svbht3Yhy0NMFOQ27g/edit "Charting even divisors"
Solution 3 - Sql
In T-SQL you can:
SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date], DATEPART(hh, [Date])
or
by minute use DATEPART(mi, [Date])
or
by 10 minutes use DATEPART(mi, [Date]) / 10
(like Timothy suggested)
Solution 4 - Sql
For a 10 minute interval, you would
GROUP BY (DATEPART(MINUTE, [Date]) / 10)
As was already mentioned by tzup and Pieter888... to do an hour interval, just
GROUP BY DATEPART(HOUR, [Date])
Solution 5 - Sql
Should be something like
select timeslot, count(*)
from
(
select datepart('hh', date) timeslot
FROM [FRIIB].[dbo].[ArchiveAnalog]
)
group by timeslot
(Not 100% sure about the syntax - I'm more an Oracle kind of guy)
In Oracle:
SELECT timeslot, COUNT(*)
FROM
(
SELECT to_char(l_time, 'YYYY-MM-DD hh24') timeslot
FROM
(
SELECT l_time FROM mytab
)
) GROUP BY timeslot
Solution 6 - Sql
The original answer the author gave works pretty well. Just to extend this idea, you can do something like
group by datediff(minute, 0, [Date])/10
which will allow you to group by a longer period then 60 minutes, say 720, which is half a day etc.
Solution 7 - Sql
For MySql:
GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);
Solution 8 - Sql
declare @interval tinyint
set @interval = 30
select dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0), sum(Value_Transaction)
from Transactions
group by dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0)
Solution 9 - Sql
If you want to actually display the date, have a variable grouping, and be able to specify larger time frames than 60 minutes:
DECLARE @minutes int
SET @minutes = 90
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Date]) / @minutes * @minutes, 0) as [Date],
AVG([Value]) as [Value]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY
DATEDIFF(MINUTE, 0, [Date]) / @minutes
Solution 10 - Sql
My solution is to use a function to create a table with the date intervals and then join this table to the data I want to group using the date interval in the table. The date interval can then be easily selected when presenting the data.
CREATE FUNCTION [dbo].[fn_MinuteIntervals]
(
@startDate SMALLDATETIME ,
@endDate SMALLDATETIME ,
@interval INT = 1
)
RETURNS @returnDates TABLE
(
[date] SMALLDATETIME PRIMARY KEY NOT NULL
)
AS
BEGIN
DECLARE @counter SMALLDATETIME
SET @counter = @startDate
WHILE @counter <= @endDate
BEGIN
INSERT INTO @returnDates VALUES ( @counter )
SET @counter = DATEADD(n, @interval, @counter)
END
RETURN
END
Solution 11 - Sql
For SQL Server 2012, though I believe it would work in SQL Server 2008R2, I use the following approach to get time slicing down to the millisecond:
DATEADD(MILLISECOND, -DATEDIFF(MILLISECOND, CAST(time AS DATE), time) % @msPerSlice, time)
This works by:
- Getting the number of milliseconds between a fixed point and target time:
@ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)
- Taking the remainder of dividing those milliseconds into time slices:
@rms = @ms % @msPerSlice
- Adding the negative of that remainder to the target time to get the slice time:
DATEADD(MILLISECOND, -@rms, time)
Unfortunately, as is this overflows with microseconds and smaller units, so larger, finer data sets would need to use a less convenient fixed point.
I have not rigorously benchmarked this and I am not in big data, so your mileage may vary, but performance was not noticeably worse than the other methods tried on our equipment and data sets, and the payout in developer convenience for arbitrary slicing makes it worthwhile for us.
Solution 12 - Sql
select dateadd(minute, datediff(minute, 0, Date), 0),
sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)
Solution 13 - Sql
In SQLite, in order to group by hour, you can do:
GROUP BY strftime('%H', [FRIIB].[dbo].[ArchiveAnalog].[Date]);
and to group by each 10 minutes:
GROUP BY strftime('%M', [FRIIB].[dbo].[ArchiveAnalog].[Date]) / 10;
Solution 14 - Sql
select from_unixtime( 600 * ( unix_timestamp( [Date] ) % 600 ) ) AS RecT, avg(Value)
from [FRIIB].[dbo].[ArchiveAnalog]
group by RecT
order by RecT;
replace the two 600 by any number of seconds you want to group.
If you need this often and the table doesn't change, as the name Archive suggests, it would probably be a bit faster to convert and store the date (& time) as a unixtime in the table.
Solution 15 - Sql
I know I am late to the show with this one, but I used this - pretty simple approach. This allows you to get the 60 minute slices without any rounding issues.
Select
CONCAT(
Format(endtime,'yyyy-MM-dd_HH:'),
LEFT(Format(endtime,'mm'),1),
'0'
) as [Time-Slice]
Solution 16 - Sql
Try this query. It makes one column. (references @nobilist answer)
GROUP BY CAST(DATE(`your_date_field`) as varchar) || ' ' || CAST(HOUR(`your_date_field`) as varchar) || ':' || CAST(FLOOR(minute(`your_date_field`) / 10) AS varchar) || '0' AS date_format