Round time to nearest x minutes or x seconds

If you collect time series data and need to normalise the times (for example, for grouping purposes) here’s some code that implements rounding to the nearest x minutes:


-- Round times to the nearest 5 minutes
DECLARE @nearestminutes int = 5

SELECT DATEADD( minute, 
                ( DATEDIFF(minute, CONVERT(char(8),create_date,112), DATEADD(second, @nearestminutes * 60 / 2, create_date)) / @nearestminutes ) * @nearestminutes,
                CONVERT(char(8),create_date,112) ) as create_date_rounded,
       DATEADD( minute, 
                ( DATEDIFF(minute, CONVERT(char(8),create_date,112), create_date) / @nearestminutes ) * @nearestminutes,
                CONVERT(char(8),create_date,112) ) as create_date_truncated,
       create_date,
       name
FROM sys.objects
ORDER BY name

You can see I show the difference between rounding and truncating:

roundtime

Change @nearestminutes to whatever you like.  For example, 60 to round to the nearest hour or 120 to round to the nearest two hours.

To round to the nearest x seconds you can use this


-- Round times to the nearest 5 seconds
DECLARE @nearestseconds int = 5

SELECT DATEADD( second,
                ( DATEDIFF(second, CONVERT(char(8),create_date,112), DATEADD(millisecond, @nearestseconds * 1000 / 2, create_date)) / @nearestseconds ) * @nearestseconds,
                CONVERT(char(8),create_date,112) ) as create_date_rounded,
       DATEADD( second,
                ( DATEDIFF(second, CONVERT(char(8),create_date,112), create_date) / @nearestseconds ) * @nearestseconds,
                CONVERT(char(8),create_date,112) ) as create_date_truncated,
       create_date,
       name
FROM sys.objects
ORDER BY name

Advertisements
  1. #1 by Jason Statham on March 26, 2015 - 5:03 am

    Thank you, works well

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: