How to group time by hour or by 10 minutes

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt    endDate             BEGIN                 INSERT INTO  returnDates VALUES    counter                   SET  counter   DATEADD n   interval   counter              END         RETURN     END

User · Answer

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

User · Answer

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     The 10 and MINUTE terms can be changed to any number and DATEPART  respectively  It is a DATETIME value  which means   It works fine across long time intervals   There is no collision between years   Including it in the SELECT statement will give your output a column with pretty output truncated at the level you specify     2000  is an  quot anchor date quot  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  quot anchor    136 years  quot  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

User · Answer

select dateadd minute  datediff minute  0  Date   0          sum SnapShotValue  FROM  FRIIB   dbo   ArchiveAnalog  group by dateadd minute  datediff minute  0  Date   0

User · Answer

For MySql   GROUP BY DATE  your date field    HOUR  your date field    FLOOR  MINUTE  your date field     10

User · Answer

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   amp  time  as a unixtime in the table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to group time by hour or by 10 minutes

Examples related to sql

Examples related to sql-server-2008

Examples related to tsql

Examples related to group-by