Add info about InfluxQL GROUP BY time() with time bounds behavior in subqueries

[sanderson]

If you use GROUP BY time() in a subquery and include time bounds in the WHERE clause, the WHERE clause has to be to be in the inner query before the GROUP BY clause, not the outer query. Otherwise the query returns no results.

For example, the following query doesn't return any results:

SELECT
  mean("price") AS "mean_price"
FROM
  (
    SELECT
      MEAN("price") AS "price"
    FROM
      "bitcoin".."coindesk"
    GROUP BY
      "code",
      time(1d)
  )
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-02-01T00:00:00Z'

However, the following query does return results:

SELECT
  mean("price") AS "mean_price"
FROM
  (
    SELECT
      MEAN("price") AS "price"
    FROM
      "bitcoin".."coindesk"
    WHERE
      time > '2023-01-01T00:00:00Z'
      AND time < '2023-02-01T00:00:00Z'
    GROUP BY
      "code",
      time(1d)
  )

Relevant URLs

• https://docs.influxdata.com/influxdb/v2/query-data/influxql/explore-data/subqueries/
• https://docs.influxdata.com/influxdb/cloud/query-data/influxql/explore-data/subqueries/