Rounding to 15 Minute Intervals

A recent PowerBI experience presented me with a situation where I wanted to round Date/Time data to the nearest 15 minute increment. Although my PBI experience isn't vast, I don't find the DAX language to be difficult (especially for those that have written formulas in Excel). I was discouraged at how unhelpful the DAX Date/Time function were. After the code was written, I stepped back, pondered what I had written, and concluded it was a verbose and unintuitive mess. Surely I could do better in T-SQL. Or could I?

Query 1

Here's the first T-SQL query I came up with. (I allowed for mid-rounding, rounding up, or rounding down to the nearest 15 minute increment to meet the rounding criteria.) It calculates the difference in minutes between the DATE part of a DATETIME, subtracts the number of minutes beyond the most recent 15 minute interval, and adds the number of minutes to the DATE part. This is arguably the most intuitive query of the bunch. But it feels like an excessive amount of code to achieve such a simple request:

DECLARE @Now DATETIME = CURRENT_TIMESTAMP

--Round down to previous 15 min increment.
SELECT 
 DATEADD(
  MINUTE,
  DATEDIFF(MINUTE, CAST(CAST(@Now AS DATE) AS DATETIME), @Now) - 
   (DATEDIFF(MINUTE, CAST(CAST(@Now AS DATE) AS DATETIME), @Now) % 15),
  CAST(CAST(@Now AS DATE) AS DATETIME)
 )

The next few queries use a tactic that might be considered hack-ish: adding a DATETIME with a number type. Adding a whole number X to a DATETIME results in a DATETIME exactly X days after (or before for a negative number). A real number would add (or subtract) portions of a day:

SELECT 
 CURRENT_TIMESTAMP + 3 AS [3 Days Ahead],
 CURRENT_TIMESTAMP + 0.5 AS [½ Day Ahead],
 CURRENT_TIMESTAMP AS [Now]
GO

3 Days Ahead             ½ Day Ahead               Now
-----------------------  -----------------------   -----------------------
2019-08-20 16:49:18.340  2019-08-18 04:49:18.340   2019-08-17 16:49:18.340

(1 row affected)

Query 2

The second query truncates a DATETIME (removes the TIME portion) by casting it to a DATE and then back to a DATETIME. Then it calculates the number of minutes in the TIME part of the DATETIME, subtracts the number of minutes beyond the most recent 15 minute interval, divides that value by 1440 (the number of minutes in a day), and adds the value to the truncated DATETIME. There's a little less code, but it's far from intuitive:

DECLARE @Now DATETIME = CURRENT_TIMESTAMP

--Round down to previous 15 min increment.
SELECT
 CAST(CAST(@Now AS DATE) AS DATETIME) + 
  ((DATEDIFF(MINUTE, CAST(CAST(@Now AS DATE) AS DATETIME), @Now) - 
  (DATEDIFF(MINUTE, CAST(CAST(@Now AS DATE) AS DATETIME), @Now) % 15)) / 1440.000)

Query 3

The third query is similar to Query 2. To calculate the number of minutes (rounded down to the nearest 15 minute increment), it makes use of the TIME data type, which reduces the number of calls to the CAST function. The code gets a little bit smaller than the previous query, but it's equally unintuitive.

DECLARE @Now DATETIME = CURRENT_TIMESTAMP

--Round down to previous 15 min increment.
SELECT 
 CAST(CAST(@Now AS DATE) AS DATETIME) + 
  ((DATEDIFF(MINUTE, 0, CAST(@Now AS TIME)) - 
  (DATEDIFF(MINUTE, 0, CAST(@Now AS TIME)) % 15)) / 1440.000)

Query 4

Unlike the others, this query (appropriately) rounds time both up and down to the nearest 15 minute increment. As the two queries before, it truncates a DATETIME (removes the TIME portion) by casting it to a DATE and then back to a DATETIME. Then the number of minutes in the TIME portion is calculated, divided by 60 to convert to hours, multiplied by four, rounded to the nearest whole number, divided by four to convert back to hours, and divided by 24 to convert to days. The amount of code is reduced once more, and made even less intuitive:

DECLARE @Now DATETIME = CURRENT_TIMESTAMP

--Round to nearest 15 minute increment.
SELECT
 CAST(CAST(@Now AS DATE) AS DATETIME) + 
  ROUND(DATEDIFF(MINUTE, 0, CAST(@Now AS TIME)) / 60.0 * 4, 0) / 4.0 / 24.0

Query 5

The last query is the smallest and the weirdest. It casts a DATETIME to NUMERIC(38, 22). This represents the number of days past Jan 1st, 1900, with the digits to the right of the decimal place representing the number of hours, minutes, seconds, and fractions of a second (if any) past midnight. This is multiplied by 24 to convert to hours, multiplied by 4, rounded to zero decimal places, divided by four to convert back to hours, divided by 24 to convert back to days, and finally cast back to a DATETIME.

DECLARE @Now DATETIME = CURRENT_TIMESTAMP;

--Round to nearest 15 minute increment.
SELECT 
 CAST(ROUND(CAST(@Now AS NUMERIC(38, 22)) * 24 * 4, 0) / 4 / 24 AS DATETIME)

If I had to choose any of these options for production, I'd probably go with Query 1, just because I think it would make the most sense to any other developer that might encounter it (including future me, who probably wouldn't remember writing the code). But I'm not really thrilled with any of the options. The one thing T-SQL has going for it though, is the relative ease for truncating the TIME off of a DATETIME by casting to DATE and back to DATETIME. I couldn't find anything like this in DAX. Speaking of which, here's what the DAX function in my PowerBI report looked like:

Nearest15 = DATE(YEAR([SomeDate]), MONTH([SomeDate]), DAY([SomeDate])) + 
HOUR([SomeDate])/24 + 
INT( MINUTE([SomeDate]) /15) * 15 / 1440

That doesn't make any more or less sense to me than the T-SQL options. Truncating the time from a Date/Time in DAX felt really awkward and uncomfortable: I had to use the clumsy DATE function, passing in the individual date parts via the YEAR, MONTH, and DAY functions. If DAX has a better way, I couldn't find it in the documentation.

.NET Framework developers are probably chuckling at all of this hand-wringing. Here's a PowerShell example showing how easy it is for them:

 #Round to nearest 15 minute interval.
[DateTime]::Today.AddHours([Math]::Round([DateTime]::Now.TimeOfDay.TotalHours * 4) / 4) 

Thanks for dropping by. May all your code be concise and easy to read!