Thursday, June 10, 2010

Bucket DateTimes in T-SQL

Today at work I was working on a SQL Server Reporting Services report which shows us the number of rows being created in a certain table in each 10-minute interval. While working on this, I tried several different ways in which I could "bucket" the datetime column into the same ten-minute period. At the end, I finally decided to extract this logic into a User-Defined Function which can bucket any datetime into any size interval.

Here's the function:




/* BucketDateTime:
* Buckets any datetime into an interval of the given size.
* Returns the minimum datetime for the interval chosen, or NULL if @MinutesPerBucket < 1
* Example: bucket times 7:29pm and 7:31pm to the nearest 30 minute interval
* SELECT dbo.BucketDateTime('2010-04-01 19:29:00', 30) => '2010-04-01 19:00:00.000'
* SELECT dbo.BucketDateTime('2010-04-01 19:31:00', 30) => '2010-04-01 19:30:00.000'
*/
CREATE FUNCTION [dbo].[BucketDateTime]
(
@SourceDateTime DATETIME, -- The DateTime to bucket
@MinutesPerBucket INT -- The number of minutes per bucket.
)
RETURNS DATETIME
AS
BEGIN

IF (@MinutesPerBucket < 1) RETURN NULL

-- The earliest possible datetime
DECLARE @Epoch DATETIME

-- # of minutes passed between the epoch and source datetime
DECLARE @MinutesSinceEpoch INT

-- The value to bucket @SourceDateTime into, based on @MinutesPerBucket
DECLARE @BucketDateTime DATETIME

-- # of minutes passed between @BucketDateTime
DECLARE @BucketMinutesSinceEpoch INT

-- This value is from Books online: http://msdn.microsoft.com/en-us/library/ms186724.aspx
SET @Epoch = CAST('1753-01-01' AS DATETIME)

SET @MinutesSinceEpoch = DATEDIFF(mi, @Epoch, @SourceDateTime)

SET @BucketMinutesSinceEpoch = @MinutesSinceEpoch - (@MinutesSinceEpoch % @MinutesPerBucket)

SET @BucketDateTime = DATEADD(mi, @BucketMinutesSinceEpoch, @Epoch)

RETURN @BucketDateTime

END

GO


2 comments:

  1. I gotta say, I love this function! Thanks for posting. This makes dividing up data into time intervals so easy... before it was kind of a pita.

    ReplyDelete
  2. Thank you so much, I was having trouble getting my head around this and I plug you function in as show below and BAM!!! Thanks again


    SELECT dbo.BucketDateTime(readingTime, 15), AVG(powerOutput)
    FROM [IPPSC].[dbo].[windTurbineReading]
    WHERE readingTime >= @today
    GROUP BY dbo.BucketDateTime(readingTime, 15)
    order by dbo.BucketDateTime(readingTime, 15)

    ReplyDelete