Search code examples
sqldruid

Druid SQL group by date

I'm writing an SQL query for Apache Druid and I would like to group results by date. I'm used to DB2 and I would typically do something like:

SELECT DATE(TIMESTAMP), COUNT(*) FROM my_table GROUP BY (DATE(TIMESTAMP))

I'm using the /druid/v2/sql API endpoint and passing in the query in a POST. I get an SQL parsing error when I try this. I know that I can group by day with something like

SELECT EXTRACT(day FROM timestamp), COUNT(*) FROM my_table GROUP BY 1

but I would like the full date if possible.

Thanks in advance.

Solution

  • Does this work?

    SELECT DATE_TRUNC('day', TIMESTAMP), COUNT(*)
FROM my_table
GROUP BY DATE_TRUNC('day', TIMESTAMP);