CODEWITHSUNDEEP
sqlsql-server-2008t-sqlgroup-by
cnd
cnd

Reputation: 33724

How can I group time by hour or by 10 minutes?

Like when I do

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date]

How can I specify the group period? I'm using MS SQL 2008.

I've tried this, both with % 10 and / 10.

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

Is it possible to make Date output without milliseconds?

Upvotes: 242

Views: 483428

Answers (18)

Michael come lately
Michael come lately

Reputation: 9323

Short and sweet

GROUP BY DATEDIFF(MINUTE, '2000', date_column) / 10

With heavy acknowledgements to Derek's answer, which1 forms the core of this one. If you're on SQL Server 2022+, go straight to Martin's answer.

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 extra commentary

Bin interval size

The MINUTE and 10 terms can be changed to any DATEPART and integer,2 respectively, to group into different time intervals. For example, 10 with MINUTE is ten minute intervals; 6 with HOUR 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

Value type

The actual values being grouped are a set of relative offsets from 2000-01-01 00:00. This means data sources over long time intervals are fine. Some other answers have collision between years.

Multiplying the GROUP BY expression by the interval size and wrapping it in a DATEADD invocation will return you a DATETIME value. Including it in the SELECT statement will give your output a single column with the truncated timestamp. See the "Practical Usage" example above.

Shifting the label for the bin interval

The division (/) operation after DATEDIFF truncates values to integers (a FLOOR shortcut), which yields the beginning of time intervals for each row in your SELECT output.

If you want to label each row with the middle or end of its interval, you can tweak the division in the second term of DATEADD with the bold part in the table below:

Labeled as Query modification Credit to
End …) / 10 * 10 + 10 , '2000') Daniel Elkington
Middle …) / 10 * 10 + (10 / 2.0) , '2000')

If you want to round your intervals inward such that each timestamp represents half an interval before and half an interval after it, use something like this:

DATEADD(MINUTE, ROUND(1. * DATEDIFF(MINUTE, '2000', date_column) / 10, 0) * 10, '2000')

Note the 1. to do untruncated division instead. You will need to modify your GROUP BY to match, and you may want to use the whole ROUND(…) expression to avoid any unexpected float rounding.

Date math 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.3

If your data spans centuries,4 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 one of the two replacements instead of '2000' wherever it appears in the query:

  • DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date]), 0)
  • CONVERT(DATETIME, CONVERT(DATE, aa.[date]))

Your query will be totally unreadable, but it will work.

1 I realized several years after posting that my code could be simplified to nearly the same as Derek's answer.
2 If you want all :00 timestamps to be eligible for binning, use an integer that your DATEPART's maximum can evenly divide into.5 As a counterexample, grouping results into 13-minute or 37-hour bins will skip some :00s, but it should still work fine.
3 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.
4 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.
5 If you ever wondered why our clocks have a 12 at the top, reflect on how 5 is the only integer 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.

Upvotes: 141

Martin Smith
Martin Smith

Reputation: 452998

For people on SQL Server 2022+ there is now a dedicated DATE_BUCKET function that addresses this need.

Example usage

DECLARE @ArchiveAnalog TABLE 
(
[Date] DATETIME2 PRIMARY KEY,
Value FLOAT
)

INSERT @ArchiveAnalog 
VALUES 
    ('2000-01-01 16:20:00.000', 1),  --Bucket 1
    ('2000-01-01 16:22:53.250', 2),
    ('2000-01-01 16:29:59.999', 3),
    ('2000-01-01 16:31:53.250', 4),  --Bucket 2
    ('2000-01-01 16:36:53.250', 5)


SELECT  
        DATE_BUCKET (minute, 10, [Date] ) AS BucketedValue,
        MIN([Date]) AS RecT, 
        COUNT(*) AS BucketCount,
        AVG(Value) AS BucketAvg
FROM @ArchiveAnalog
GROUP BY DATE_BUCKET (minute, 10, [Date] )
ORDER BY DATE_BUCKET (minute, 10, [Date] )
BucketedValue RecT BucketCount BucketAvg
2000-01-01 16:20:00.0000000 2000-01-01 16:20:00.0000000 3 2
2000-01-01 16:30:00.0000000 2000-01-01 16:31:53.2500000 2 4.5

If the underlying column is indexed this can also be pretty efficient

enter image description here

Upvotes: 7

quemeful
quemeful

Reputation: 9848

Here is an option that provides a human readable start time of that interval (7:30, 7:40, etc).

In a temp table, it truncates seconds and milliseconds by using SMALLDATETIME, and then the main query subtracts any amount over the desired minute interval.

SELECT DATEADD(MINUTE, -(DATEDIFF(MINUTE, '2000', tmp.dt) % 10), tmp.dt)
FROM (
    SELECT CAST(DateField AS SMALLDATETIME) AS dt
    FROM MyDataTable
) tmp

It can also be done in a single line of code, but it is not as readable.

SELECT DATEADD(MINUTE, -(DATEDIFF(MINUTE, '2000', CAST(DateField AS SMALLDATETIME)) % 10), CAST(DateField AS SMALLDATETIME)) AS [interval] FROM MyDataTable

Upvotes: 0

Nicow
Nicow

Reputation: 445

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

Upvotes: 4

tsveti_iko
tsveti_iko

Reputation: 7972

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;

Upvotes: 3

insung
insung

Reputation: 111

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

Upvotes: 0

Jakub Dvorak
Jakub Dvorak

Reputation: 77

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

Upvotes: 0

jesse
jesse

Reputation: 3

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]

Upvotes: 0

Rodas PT
Rodas PT

Reputation: 339

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)

Upvotes: 3

theking2
theking2

Reputation: 2803

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.

Upvotes: 0

Derek
Derek

Reputation: 91

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.

Upvotes: 9

Timothy Khouri
Timothy Khouri

Reputation: 31845

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])

Upvotes: 14

tychon
tychon

Reputation: 580

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.

Upvotes: 1

N..
N..

Reputation: 129

For MySql:

GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);

Upvotes: 7

tzup
tzup

Reputation: 3604

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)

Upvotes: 23

user3193141
user3193141

Reputation: 11

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

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

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

Upvotes: 8

cnd
cnd

Reputation: 33724

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)

Upvotes: 279

Related Questions