Minute Breakdown (as datetime values) Between 2 Datetime Columns

Question

This is different than other 'need date difference posts' I've found bc I need to parse out the difference between dates by the minute. I tried solutions in this post and others with no luck.

I have this 'UnavailableTime' table

UnavailableID	UnavailableFrom	UnavailableTo
1	2/1/2023 8:00:00 AM	2/1/2023 8:03:00 AM
2	2/2/2023 10:30:00 AM	2/2/2023 10:32:00 AM

I need a minute breakdown for each timeframe the person was unavailable. Like this:

UnavailableID	MinuteBreakdown
1	2/1/2023 8:00:00 AM
1	2/1/2023 8:01:00 AM
1	2/1/2023 8:02:00 AM
1	2/1/2023 8:03:00 AM
2	2/2/2023 10:30:00 AM
2	2/2/2023 10:31:00 AM
2	2/2/2023 10:32:00 AM

Could someone please help me!

I've tried joining my 'UnavailableTime' table to a calender table that has every minute for the month, then doing a count of minutes inbetween the From & To times with no luck.

What exactly you have tried? – Maciej Los Mar 09 '23 at 19:38 — Maciej Los, Mar 09 '23 at 19:38

score 1 · Accepted Answer · answered Mar 09 '23 at 19:45

You don't need a calendar table for minutes because there's nothing really special about any minute between two points in time (unless you need to compensate for the night of a change to or from DST, but let's pretend everyone stores UTC because... that makes much more sense).

We can generate a dynamic numbers table using recursion (or sub in a numbers table you already have, or generate one using a variety of other methods that are easy to search for). We only need the largest gap, so we can determine that up front. Then we just join to all of the "minutes to add" that are less than or equal to the minute gap between any row's From / To values.

DECLARE @maxDelta int;

SELECT @maxDelta = MAX(DATEDIFF(MINUTE, UnavailableFrom, UnavailableTo))
FROM dbo.UnavailableTime;

WITH m(m) AS
(
  SELECT 0 UNION ALL SELECT m+1
  FROM m WHERE m < @maxDelta
)
SELECT u.UnavailableID, 
  MinuteBreakdown = DATEADD(MINUTE, m.m, u.UnavailableFrom)
FROM dbo.UnavailableTime AS u
INNER JOIN m ON m.m <= DATEDIFF(MINUTE, u.UnavailableFrom, u.UnavailableTo)
ORDER BY UnavailableID, MinuteBreakdown
OPTION (MAXRECURSION 32767);

Example db<>fiddle

This works until you get to about 22 days of outage, then you need to change 32767 to 0.

This worked wonderfully!! Thank you so much! And as always, I appreciate the corresponding fiddle explanation! — Laura Bullard, Mar 09 '23 at 21:09

score 0 · Answer 2 · answered Mar 09 '23 at 19:46

This can do the trick (as long as you don't have many, many rows, First I calculate the number of minutes between UnavailableFrom and UnavailableTo. then for each UnavailableID I create a numerator till the number of minutes

SELECT UnavailableID, MinuteBreakdown 
FROM MyTable
CROSS APPLY (SELECT DATEDIFF(MINUTE, UnavailableFrom,UnavailableTo) MinuteCount ) ca
CROSS APPLY (SELECT TOP (MinuteCount) DATEADD(MINUTE, ROW_NUMBER() OVER( ORDER BY (SELECT NULL))-1, UnavailableFrom ) MinuteBreakdown FROM sys.all_objects) ca2

score 0 · Answer 3 · answered Mar 09 '23 at 19:54

You can use Common Table Expressions:

;WITH CTE AS
(
  -- initial part
  SELECT UnavailableID, UnavailableFrom AS MinuteBreakdown, UnavailableFrom, UnavailableTo
  FROM TimeMachine
  WHERE UnavailableFrom < UnavailableTo
  UNION ALL
  -- recursive part
  SELECT UnavailableID, DATEADD(MI, 1, MinuteBreakdown), UnavailableFrom, UnavailableTo
  FROM CTE
  WHERE DATEADD(MI, 1, MinuteBreakdown) <= UnavailableTo
)
SELECT *
FROM CTE
ORDER BY UnavailableID, MinuteBreakdown;

SQL Fiddle

Keep in mind this breaks if any outage can be more than 100 minutes, [example](https://dbfiddle.uk/I70Qgk-q). — Aaron Bertrand, Mar 09 '23 at 20:52

Minute Breakdown (as datetime values) Between 2 Datetime Columns

3 Answers3