blocks|key|2989427|text|在T-SQL中，您可以：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2989428|SELECT+[Date]
++FROM+[FRIIB].[dbo].[ArchiveAnalog]
++GROUP+BY+[Date],+DATEPART(hh,+[Date])|code-block|syntax|javascript|2989429|或|2989430|按分钟使用DATEPART(mi,+[Date])|offset|length|style|CODE|2989431|2989432|10分钟后使用DATEPART(mi,+[Date])+/+10+(按照蒂莫西的建议)|2989433|entityMap^0|0|0|0|5|K|0|0|7|P|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|W|8|@$K|X|L|Y|M|N]]|9|@]|A|$]]|$1|O|3|H|5|6|7|Z|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|10|8|@$K|11|L|12|M|N]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|S|$]]

In T-SQL you can:

<pre><code>SELECT [Date]
 FROM [FRIIB].[dbo].[ArchiveAnalog]
 GROUP BY [Date], DATEPART(hh, [Date])
</code></pre>

or

by minute use <code>DATEPART(mi, [Date])</code>

or 

by 10 minutes use <code>DATEPART(mi, [Date]) / 10</code> (like Timothy suggested)

blocks|key|281494|text|在10分钟的间隔内，您将|type|unstyled|depth|inlineStyleRanges|entityRanges|data|281495|GROUP+BY+(DATEPART(MINUTE,+[Date])+/+10)|code-block|syntax|javascript|281496|正如tzup和Pieter888已经提到的.要做一个小时间隔，只需|281497|GROUP+BY+DATEPART(HOUR,+[Date])|281498|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For a 10 minute interval, you would

<pre><code>GROUP BY (DATEPART(MINUTE, [Date]) / 10)
</code></pre>

As was already mentioned by tzup and Pieter888... to do an hour interval, just

<pre><code>GROUP BY DATEPART(HOUR, [Date])
</code></pre>

blocks|key|3203022|text|应该是这样的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3203023|select+timeslot,+count(*)++
from+
++++(
++++select+datepart('hh',+date)+timeslot
++++FROM+[FRIIB].[dbo].[ArchiveAnalog]++
++++)+
group+by+timeslot|code-block|syntax|javascript|3203024|(语法不是100%25确定--我更像是一个Oracle类型的人)|3203025|在Oracle中：|3203026|SELECT+timeslot,+COUNT(*)+
FROM
(++
++++SELECT+to_char(l_time,+'YYYY-MM-DD+hh24')+timeslot+
++++FROM
++++(
++++++++SELECT+l_time+FROM+mytab++
++++)++
)+GROUP+BY+timeslot+|3203027|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Should be something like

<pre><code>select timeslot, count(*) 
from 
 (
 select datepart('hh', date) timeslot
 FROM [FRIIB].[dbo].[ArchiveAnalog] 
 ) 
group by timeslot
</code></pre>

(Not 100% sure about the syntax - I'm more an Oracle kind of guy)

In Oracle:

<pre><code>SELECT timeslot, COUNT(*) 
FROM
( 
 SELECT to_char(l_time, 'YYYY-MM-DD hh24') timeslot 
 FROM
 (
 SELECT l_time FROM mytab 
 ) 
) GROUP BY timeslot 
</code></pre>

blocks|key|283559|text|最终完成了|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283560|GROUP+BY
DATEPART(YEAR,+DT.[Date]),
DATEPART(MONTH,+DT.[Date]),
DATEPART(DAY,+DT.[Date]),
DATEPART(HOUR,+DT.[Date]),
(DATEPART(MINUTE,+DT.[Date])+/+10)|code-block|syntax|javascript|283561|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

finally done with

<pre><code>GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)
</code></pre>

blocks|key|3203120|text|我的解决方案是使用一个函数创建一个包含日期间隔的表，然后使用表中的日期间隔将该表连接到我想要分组的数据。然后，在显示数据时可以很容易地选择日期间隔。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3203121|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|code-block|syntax|javascript|3203122|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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 &lt;= @endDate
 BEGIN
 INSERT INTO @returnDates VALUES ( @counter )
 SET @counter = DATEADD(n, @interval, @counter)
 END
 RETURN
 END
</code></pre>

blocks|key|277991|text|作者给出的原始答案工作得很好。只是为了扩展这个想法，你可以这样做|type|unstyled|depth|inlineStyleRanges|entityRanges|data|277992|group+by+datediff(minute,+0,+[Date])/10|code-block|syntax|javascript|277993|这将允许您按超过60分钟的更长时间进行分组，例如720，即半天等。|277994|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The original answer the author gave works pretty well. Just to extend this idea, you can do something like 

<pre><code>group by datediff(minute, 0, [Date])/10
</code></pre>

which will allow you to group by a longer period then 60 minutes, say 720, which is half a day etc.

blocks|key|2989612|text|对于MySql：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2989613|GROUP+BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR(+MINUTE(`your_date_field`)+/+10);|code-block|syntax|javascript|2989614|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For MySql:

<pre><code>GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);
</code></pre>

blocks|key|281583|text|对于SQL+Server+2012，尽管我相信它可以在SQL+Server+2008R2中工作，但我使用以下方法将时间切片缩短到毫秒：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|281584|DATEADD(MILLISECOND,+-DATEDIFF(MILLISECOND,+CAST(time+AS+DATE),+time)+%25+@msPerSlice,+time)|code-block|syntax|javascript|281585|它的工作原理是：|281586|281587|获取固定点和目标时间之间的毫秒数：|unordered-list-item|281588|281589|@ms+=+DATEDIFF(MILLISECOND,+CAST(time+AS+DATE),+time)|offset|length|style|CODE|281590|Taking将这些毫秒分成时间片的剩余部分：|281591|281592|将余数的负数@rms+=+@ms+%25+@msPerSlice|281593|Adding到目标时间，以获得切片时间：|281594|281595|DATEADD(MILLISECOND,+-@rms,+time)|281596|不幸的是，由于微秒和更小的单位溢出，因此更大、更精细的数据集将需要使用不太方便的固定点。|281597|我没有严格地对此进行基准测试，也不是在大数据领域，所以您的里程数可能会有所不同，但性能并不比在我们的设备和数据集上尝试的其他方法差太多，并且在开发人员方便任意切片方面的支出使我们觉得值得。|281598|entityMap^0|0|0|0|0|0|0|0|1H|0|0|0|6|O|0|0|0|0|X|0|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1B|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1D|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1F|8|@$P|1G|Q|1H|R|S]]|9|@]|A|$]]|$1|T|3|U|5|L|7|1I|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1K|8|@$P|1L|Q|1M|R|S]]|9|@]|A|$]]|$1|Y|3|Z|5|L|7|1N|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1P|8|@$P|1Q|Q|1R|R|S]]|9|@]|A|$]]|$1|13|3|14|5|6|7|1S|8|@]|9|@]|A|$]]|$1|15|3|16|5|6|7|1T|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|18|$]]

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:

<pre><code>DATEADD(MILLISECOND, -DATEDIFF(MILLISECOND, CAST(time AS DATE), time) % @msPerSlice, time)
</code></pre>

This works by:

<ul>
<li>Getting the number of milliseconds between a fixed point and target time: <code>@ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)</code></li>
<li>Taking the remainder of dividing those milliseconds into time slices: <code>@rms = @ms % @msPerSlice</code></li>
<li>Adding the negative of that remainder to the target time to get the slice time: <code>DATEADD(MILLISECOND, -@rms, time)</code></li>
</ul>

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.

blocks|key|3203236|text|select+from_unixtime(+600+*+(+unix_timestamp(+[Date]+)+%25+600+)+)+AS+RecT,+avg(Value)
from+[FRIIB].[dbo].[ArchiveAnalog]
group+by+RecT
order+by+RecT;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3203237|将两个600替换为要分组的任意秒数。|unstyled|3203238|如果您经常需要这样做，并且表没有更改，正如名称Archive所暗示的那样，将日期(&+time)转换并存储为表中的unixtime可能会更快一些。|3203239|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>select from_unixtime( 600 * ( unix_timestamp( [Date] ) % 600 ) ) AS RecT, avg(Value)
from [FRIIB].[dbo].[ArchiveAnalog]
group by RecT
order by RecT;
</code></pre>

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 (&amp; time) as a unixtime in the table.

blocks|key|3203243|text|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)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3203244|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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)
</code></pre>

blocks|key|279878|text|我知道我来晚了，但我用了这个--非常简单的方法。这使您可以获得60分钟的切片，而不会出现任何舍入问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279879|Select+
+++CONCAT(+
++++++++++++Format(endtime,'yyyy-MM-dd_HH:'),++
++++++++++++LEFT(Format(endtime,'mm'),1),
++++++++++++'0'+
++++++++++)+as+[Time-Slice]|code-block|syntax|javascript|279880|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>Select 
 CONCAT( 
 Format(endtime,'yyyy-MM-dd_HH:'), 
 LEFT(Format(endtime,'mm'),1),
 '0' 
 ) as [Time-Slice]
</code></pre>

blocks|key|278038|text|select+dateadd(minute,+datediff(minute,+0,+Date),+0),
+++++++sum(SnapShotValue)
FROM+[FRIIB].[dbo].[ArchiveAnalog]
group+by+dateadd(minute,+datediff(minute,+0,+Date),+0)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|278039|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select dateadd(minute, datediff(minute, 0, Date), 0),
 sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)
</code></pre>

blocks|key|283708|text|如果您希望实际显示日期，并希望有一个变量分组，并且希望能够将较大的时间范围指定为60分钟：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283709|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|code-block|syntax|javascript|283710|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you want to actually display the date, and want to have a variable grouping, and want to be able to specify larger time frames as 60 minutes:
<pre><code>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
</code></pre>

blocks|key|283718|text|尝试此查询。它构成了一列。(参考@nobilist答案)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283719|GROUP+BY+CAST(DATE(`your_date_field`)+as+varchar)+%7C%7C+'+'+%7C%7C+CAST(HOUR(`your_date_field`)+as+varchar)+%7C%7C+':'+%7C%7C+CAST(FLOOR(minute(`your_date_field`)+/+10)+AS+varchar)+%7C%7C+'0'+AS+date_format|code-block|syntax|javascript|283720|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this query. It makes one column. (references @nobilist answer)
<pre><code>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
</code></pre>

blocks|key|279924|text|在SQLite中，为了按小时分组，您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279925|GROUP+BY+strftime('%25H',+[FRIIB].[dbo].[ArchiveAnalog].[Date]);|code-block|syntax|javascript|279926|并按每10分钟分组：|279927|GROUP+BY+strftime('%25M',+[FRIIB].[dbo].[ArchiveAnalog].[Date])+/+10;|279928|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In SQLite, in order to group by hour, you can do:
<pre><code>GROUP BY strftime('%H', [FRIIB].[dbo].[ArchiveAnalog].[Date]);
</code></pre>
and to group by each 10 minutes:
<pre><code>GROUP BY strftime('%M', [FRIIB].[dbo].[ArchiveAnalog].[Date]) / 10;
</code></pre>

like when I do 

<pre><code>SELECT [Date]
 FROM [FRIIB].[dbo].[ArchiveAnalog]
 GROUP BY [Date]
</code></pre>

how can I specify the group period ?

MS SQL 2008

2nd Edit

I'm trying 

<pre><code>SELECT MIN([Date]) AS RecT, AVG(Value)
 FROM [FRIIB].[dbo].[ArchiveAnalog]
 GROUP BY (DATEPART(MINUTE, [Date]) / 10)
 ORDER BY RecT
</code></pre>

changed %10 to / 10. is it possible to make Date output without milliseconds ?

How to group time by hour or by 10 minutes 

SQL Server

大数据

SQLite

就像我这样做的时候SELECT [Date]  FROM [FRIIB].[dbo].[ArchiveAnalog]  GROUP BY [Date]如何指定群周期？MS SQL 2008第二次编辑我在努力SELECT MIN([Date]) AS RecT, AVG(Value)  FROM [FRIIB].[dbo...

问如何按小时或按10分钟分组时间
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何按小时或按10分钟分组时间EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何按小时或按10分钟分组时间
EN