SQL : Get the duration between start DateTime and End DateTime from Table entries

I have a SQL table which has entries of machine start and end time (DateTime) with duration of machine running. Given a start DateTime and End DateTime I want to calculate the duration for which machine was running. DB is SQL and Code is in C#.

Note: THere might be some duration when the machine is not running in between the given start and end DateTime, I want to ignore that duration during the total calculation.

Consider example

Start Time Machine	End Time Machine	Duration(Mins)
1:00:01	2:00:00	– 60
2:00:01	2:30:00	– 30
3:00:01	4:00:00	– 60

If the selected Start time is 1:40:00 and End time is 3:20:00( notice that machine did not run from 2:30:00 to 3:00)

how to get the duration in SQL?

For above example the answer should be 70 mins.

1:40:00 to 2:30:00 - 50Mins
3:00:00 to 3:20:00 - 20Mins
Total = 70mins  between 1:40:00 and 3:20:00

Answer

here is how you can do it , I wrote in sql server (assuming you are in microsoft eco system) but you can do the same thing in any other dbms)

select sum(datediff(minute, case when startdate <@startdate then @startdate else startdate end,case when enddate < @enddate then enddate else @enddate end))
from cte
where @startdate <= enddate and @enddate >= startdate

db<>fiddle here

in postgresql you can just do this :

select extract( epoch from sum(case when enddate < enddatevar then enddate else enddatevar end - case when startdate <startdatevar then startdatevar else startdate end)) /60 
from cte
where startdatevar <= enddate and enddatevar >= startdate