Google Sheets query, calculate time

Question

I have a query with the following columns C(date), D(start time), E(end time)

In the query I would like to find the lowest start time and the highest end time for each day and subtract the start time from the end time, so I get the hours between the two times.

I hope someone can help me get the formula to work.

=QUERY('Alt data'!A2:H;"SELECT C, max(E), min(D), max(E)-min(D)
WHERE H is not null
AND C >= date '"&TEXT(B19;"YYYY-MM-DD")&"' 
AND 
C <= date '"&TEXT(C19;"YYYY-MM-DD")&"'
And
B = '"&C32&"'

GROUP BY C
ORDER BY C desc
LABEL C 'Dato'
Label max(E)-min(D) 'Hours'
";0)

Answer 1

In a query(), you can subtract one pure number from another, but cannot directly get the difference between two time values. To do that, convert start times and end times to pure values, then format the result as a duration, like this:

=let( 
  dates, filter(C2:C, isdate_strict(C2:C)), 
  durations, filter(E2:E - D2:D, isdate_strict(C2:C)), 
  query( 
    hstack(dates, durations), 
    "select Col1, max(Col2) 
     group by Col1 
     format max(Col2) '[h]:mm' ", 
    0 
  ) 
)

Alternatively, use filter(), like this:

=let( 
  dates, sort(unique(tocol(C2:C, 1))), 
  map(dates, lambda(d, let( 
    start, min(filter(D2:D, C2:C = d)), 
    end, max(filter(E2:E, C2:C = d)), 
    duration, end - start, 
    hstack( 
      to_date(d), 
      query(duration, "format Col1 '[h]:mm' ", 0) 
    ) 
  ))) 
)

See let(), unique(), tocol(), map(), filter(), hstack(), to_date() and query().