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 ?
This question is related to
sql
sql-server-2008
tsql
group-by
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:
@ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)
@rms = @ms % @msPerSlice
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.
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])
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
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)
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)
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]
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
select dateadd(minute, datediff(minute, 0, Date), 0),
sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)
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.
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.
I'm super late to the party, but this doesn't appear in any of the existing answers:
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', date_column) / 10 * 10, '2000')
10
and MINUTE
terms can be changed to any number and DATEPART
, respectively.DATETIME
value, which means:
SELECT
statement will give your output a column with pretty output truncated at the level you specify.'2000'
is an "anchor date" around which SQL will perform the date math. Jereonh discovered below that you encounter an integer overflow with the previous anchor (0
) when you group recent dates by seconds or milliseconds.†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
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
ORDER BY [date_truncated]
If your data spans centuries,‡ using a single anchor date for second- or millisecond grouping will still encounter the overflow. If that is happening, 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.
† 232 ˜ 4.29E+9, so if your DATEPART
is SECOND
, you get 4.3 billion seconds on either side, or "anchor ± 136 years." Similarly, 232 milliseconds is ˜ 49.7 days.
‡ 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.
For MySql:
GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);
Source: Stackoverflow.com