period granularity

[philippesg]

Hello, how to set period granularity with SQL queries ?

Below, an example of my old druid datasource :

Merci par avance.

[jbguerraz]

Hello @philippesg
Period granularity (https://druid.apache.org/docs/latest/querying/granularities.html#period-granularities) is not a SQL thing but a native query one.
Using SQL you can probably use something like GROUP BY TIME_FLOOR(.., see https://druid.apache.org/docs/latest/querying/sql.html#time-functions
Hope it helps!

[jbguerraz]

If that's all right for you @philippesg feel free to close that issue :) thank you!

[philippesg]

Hello, sorry for the delay @jbguerraz ;)

I tried to use different query for the function TIME_FLOOR() and I always get an error...

Have you an idea to help me ? Here, an example of what I tried :

SELECT TIME_FLOOR(__time,'P1D',0,"Europe/Paris") as "time", sum(metric_value_sum) as "number"
FROM mydatasource
GROUP BY 1

Thanks in advance,
Best regards.

[jbguerraz]

Hello @philippesg ,

Give that a try:

SELECT TIME_FLOOR(__time,'P1D', MILLIS_TO_TIMESTAMP(0), 'Europe/Paris') as "time", sum(metric_value_sum) as "number"
FROM "mydatasource"
GROUP BY 1

you have to wrap strings with simple quotes not double ones, and origin expect a value of type timestamp (hence the MILLIS_TO_TIMESTAMP), other than that, you're good to go I believe :)

Hope it helps!