How to group result based on time intervals

SQL++
15

“createdAt”: “2023-08-07T01:00:00”
You don’t have timezone , when converted to MILLIS to STR, STR to MILLIS it might use query service timezone. Check those.

p.createdAt >= “2023-08-07T01:00:00” AND p.createdAt < “2023-08-07T05:00:00”
Your time start 1:00 say you have 2 hours it will consider (IDIV of 2 hours) 00:00:00 to 02:00:00 , …
You may loose 1 hour data in count
Same is true for higher hours or days.
It all depends on what you want how you calculate.

The following query generates based on start time on the predicate upto milliseconds.
Next one increments by interval minutes

If using 3 days set intrvl 3*24*60 (i.e. 3 days * 24 hours * 60 minutes)

$stime "2023-08-07T01:10:12.123Z"
$etime "2023-08-07T05:10:17.123Z"
$intrvl 15

SELECT SUM(partySize) AS count, MILLIS_TO_TZ(time,"UTC") AS createdAt
FROM (SELECT d AS time, MILLIS_TO_TZ(d,"UTC") AS t, 0 AS partySize
      FROM ARRAY_RANGE(STR_TO_MILLIS($stime), STR_TO_MILLIS($etime), $intrvl*60*1000) AS d

      UNION ALL

      SELECT (IDIV((STR_TO_MILLIS(createdAt) - STR_TO_MILLIS($stime)),$intrvl*60*1000)*$intrvl*60*1000)+STR_TO_MILLIS($stime) AS time,
            TO_NUMBER(partySize) AS partySize
      FROM [{ "createdAt": "2023-08-07T01:00:00Z", "name": "andrea" , "partySize":"1", "tp":"qp"},
            { "createdAt": "2023-08-07T01:10:00Z", "name": "alex" , "partySize":"1", "tp":"qp"},
            { "createdAt": "2023-08-07T03:10:00Z", "name": "bob" , "partySize":"1", "tp":"qp"},
            { "createdAt": "2023-08-07T03:15:00Z", "name": "ben" , "partySize":"5", "tp":"qp"},
            { "createdAt": "2023-08-07T04:15:00Z", "name": "chris" , "partySize":"1", "tp":"qp"},
            { "createdAt": "2023-08-07T03:03:00Z", "name": "bacca" , "partySize":"1", "tp":"qp"},
            { "createdAt": "2023-08-07T03:17:00Z", "name": "buchi" , "partySize":"1", "tp":"qp"},
            { "createdAt": "2023-08-07T01:10:00Z", "name": "andy duplicate" , "partySize":"1", "tp":"qp"}] AS p
      WHERE p.createdAt BETWEEN $stime AND $etime ) AS t
GROUP BY time
ORDER BY createdAt;

Output removed 0 count
  {
        "count": 2,
        "createdAt": "2023-08-07T02:55:12.123Z"
    },
    {
        "count": 6,
        "createdAt": "2023-08-07T03:10:12.123Z"
    },
    {
        "count": 1,
        "createdAt": "2023-08-07T04:10:12.123Z"
    }