GitHub user dosubot[bot] added a comment to the discussion: Time Grains
<!-- Greeting --> Hi @DavidS4777! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the superset team. <!-- Answer --> The error you’re seeing—`Column ... is not under aggregate function and not in GROUP BY keys`—means that your SQL query is selecting a column (`start_time`) that is neither aggregated nor included in the `GROUP BY` clause. This is a common SQL requirement when using aggregate functions like `SUM()`. In Superset, when you use a time grain (like day or month), it transforms your datetime column using a function such as `toStartOfHour(toDateTime(start_time))` and expects that transformed column to be used both in the `SELECT` and `GROUP BY` clauses. The error usually occurs if the original column (not the transformed one) is referenced in the `SELECT` without being grouped or aggregated. **Key points and solutions:** - Make sure your datetime column (`start_time`) is of a recognized datetime type in your database. If you cast it to a string, Superset will not offer time grain options, as time grains require a temporal column. - The time grain transformation (e.g., `toStartOfHour(toDateTime(start_time))`) should be the only reference to your datetime column in the `SELECT` and `GROUP BY` clauses. Do not include the raw `start_time` unless you also group by it. - If you are using ClickHouse (as suggested by the function names in your error), ensure your column is stored as a `DateTime` or `Date` type. If it’s a string, convert it in the database or use a calculated column that parses it to a datetime. - Superset’s time grain controls (`time_grain_sqla`) are only available for columns recognized as temporal. If you use a string, these controls will disappear, as you observed. **Example fix:** If your column is already a datetime, Superset should generate a query like: ```sql SELECT toStartOfDay(start_time) AS start_time, sum(timeamount) AS "SUM(timeamount)" FROM db.wfm_Schedule_Activity2 GROUP BY toStartOfDay(start_time) ORDER BY "SUM(timeamount)" DESC LIMIT 10000 ``` If you see the raw `start_time` in the `SELECT` without the transformation, check your chart’s configuration and ensure you are not adding the original column as a group by or display column. **Related documentation and threads:** - Superset time grain controls and their requirements are described in the code and documentation. The time grain options are only available for datetime columns, and the backend applies the correct SQL transformation per engine. See [discussion on time grain handling](https://github.com/apache/superset/issues/33033) and [code reference for time grain controls](https://github.com/apache/superset/blob/a7aa8f7cefce61d84e84b19c2cd3f09f87fd4c1b/superset-frontend/src/explore/controls.jsx). - If you need to add or customize time grains for your database, you can do so in the Superset configuration, as described [here](https://github.com/apache/superset/blob/a7aa8f7cefce61d84e84b19c2cd3f09f87fd4c1b/superset/config.py). **Summary:** Keep your datetime column as a datetime type, use the time grain controls, and ensure only the transformed datetime appears in your `SELECT` and `GROUP BY`. If you need to convert a string to datetime, do it in the database or as a calculated column, not in the Superset UI. If you’re still seeing issues, double-check the column type in your database and the chart’s group by settings. If you have a custom SQL query, ensure it follows the pattern above. For more details, see the [related issue and solution](https://github.com/apache/superset/issues/33033). <!-- Dosu Comment Footer --> *To reply, just mention my name [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/ffaa090e-701d-4033-b71e-c186856d1b64?feedback_type=other)</sup> [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/33692) GitHub link: https://github.com/apache/superset/discussions/33692#discussioncomment-13371773 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
