[SOLVED] How do I do basic date grouping operations in Google Sheets?

How do I do basic date grouping operations in Google Sheets?

I have data that looks like this (sample):

Col A	Col B	Col C
Value 1	Value 2	1/4/24 4:15 pm
Value 3	Value 4	1/4/24 6:30 pm
Value 5	Value 6	1/5/24 2:30 pm
Value 7	Value 8	1/5/24 3:30 pm
Value 9	Value 10	1/5/24 5:30 pm

I'm trying to do something that, in proper SQL, should be pretty straightforward: "for each date Col C, not date/time, in the table, find the number of hours including decimal hours elapsed between the earliest and latest event on that date." The values in Col A and Col B are not relevant.

Net result is I want this to come back, or something similar (comment just for readability here):

Date	Hours	Comment
1/4/24	2.25	6:30pm - 4:15pm
1/5/24	3	5:30pm - 2:30pm, because 3:30pm isn't a min or max

In SQL, I'd guess the query would be like this, with some pseudocode:

SELECT
   number_of_hours(MAX(col_c) - MIN(col_c))
FROM
   my_table
GROUP BY
   DATE(col_c)

I can't figure out how to do this in Google Sheets. Given I've thought of the problem more like a database problem than something you'd do in a spreadsheet, I tried the QUERY function but everything I do ends up with an invalid query. I'm thinking Google Sheets doesn't allow you to extract the date portion of a date/time and aggregate it.

Things I've tried but failed:

=query(A:C, "SELECT A, B, C, " & TEXT(C, "yyyy-mm-dd"), 1): Formula parse error
=query(A:C, "SELECT A, B, C, " & DATE(C), 1): Formula parse error
=query(A:C, "SELECT C, COUNT(B) GROUP BY " & DATE(C), 1): Formula parse error
=query(A:C, "SELECT C, COUNT(B) GROUP BY DATE(C)", 1): Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 33
=query(A:C, "SELECT C, DATE '" & YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C) & "'", 1): Formula parse error
=query(A:C, "SELECT C, " & DATEVALUE(YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C)), 1): Formula parse error
=query(A:C, "SELECT C, DATE(YEAR(C), MONTH(C), DAY(C))", 1): Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 15

So how can I go about doing this?

Solution

You can use:

=SORT(LET(
   c,TOCOL(C2:C,1),
   d,TEXT(c,"m/d/yy"),
   MAP(UNIQUE(d),LAMBDA(u,LET(
    f,FILTER(c,d=u)*24,
    {u,MAX(f)-MIN(f)})))))