Druid: Full ISO 8601 compatibility in query intervals

[sascha-coenen]

The current Druid documentation states in several places that queries have a mandatory "intervals" or "interval" attribute which can contain ISO-8601 compatible intervals.

For instance the documentation on the groupby query (http://druid.io/docs/latest/querying/groupbyquery.html)
states: "intervals : A JSON Object representing ISO-8601 Intervals. This defines the time ranges to run the query over"

According to the following wikipedia source (https://en.wikipedia.org/wiki/ISO_8601#Time_intervals), there are four valid forms of ISO-8601 intervals:

 <start>/<end>
 <start>/<duration>
 <duration>/<end>
 <duration>

It seems that Druid only supports the first three variants. When specifying a duration only, e.g. "P2D", then Druid returns an exception saying that the "/" character is required within each interval.

I believe that supporting the last interval format which is a duration relative to the current time (or latest data present in Druid) would be a very useful addition. Most queries involve relative dateranges. If forcing people to use absolute dates, every query needs to be templated such that the timeintervals gets computed by some external logic and then inserted into the query. If the relative duration format was supported, many queries could work without the need of templated injections of external metadata.

[vogievetsky]

Just wanted to note here that this is possible in DSQL:

You can issue a query like:

SELECT "channel", COUNT(*) AS "Count"
FROM "wikipedia"
WHERE "__time" >= CURRENT_TIMESTAMP - INTERVAL '1' DAY
GROUP BY "channel"
ORDER BY "Count" DESC

for example to get the last day of data. In general doing computations with CURRENT_TIMESTAMP can be very powerful.

[stale[bot]]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 2 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the [email protected] list. Thank you for your contributions.

[stale[bot]]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 4 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the [email protected] list. Thank you for your contributions.

[stale[bot]]

This issue has been closed due to lack of activity. If you think that is incorrect, or the issue requires additional review, you can revive the issue at any time.

[ryanmorris708]

I found this issue which relates to a problem that I am facing. Metabase (and potentially other dashboard-building software) does not support DruidSQL, and therefore we can't write native queries for data from purely relative time intervals (such as "P2D"). This is partly a Metabase problem, of course. But, rather than relying on software that connects to Druid to support DSQL, it would be very helpful to have relative time intervals supported in all query languages that Druid uses.