How to dynamically add months in Apache drill

[Bharani Manickam <Bh...@specsavers.com>]

Hello,

DATE_ADD function doesn't support a column as the interval argument in drill queries.

We have a requirement to pass a column as Interval Month to derive a forecasted date. Do you have any work around for this please?

The requirement is something like this -


Query that works
select
  a,
  DATE_ADD(date '2015-05-15', interval '1' month)
from
(
select '1' a, INSERTED_AT from dfs.data.bi_interaction limit 3
);

Query that doesn't work

select

  a,

  DATE_ADD(date '2015-05-15', interval a month)

from

(

 select '1' a, INSERTED_AT from dfs.data.bi_interaction limit 3

);



Thanks
Bharani

Re: How to dynamically add months in Apache drill

[Arjun kr <ar...@outlook.com>]

One way to do this is given below. This requires interval expression to be passed to the DATE_ADD function.

0: jdbc:drill:> select DATE_ADD(date '2015-05-15', interval '1' month) with_literal,  DATE_ADD(date '2015-05-15',cast(concat('P',val,'M') as interval month)) with_column_value  from (select 1 as val from (values(1))) a;
+------------------------+------------------------+
|      with_literal      |   with_column_value    |
+------------------------+------------------------+
| 2015-06-15 00:00:00.0  | 2015-06-15 00:00:00.0  |
+------------------------+------------------------+
1 row selected (0.21 seconds)
0: jdbc:drill:>


https://drill.apache.org/docs/data-type-conversion/#casting-intervals

https://drill.apache.org/docs/date-time-and-timestamp/#intervalyear-and-intervalday

https://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_add

Thanks,

Arjun


________________________________
From: Bharani Manickam <Bh...@specsavers.com>
Sent: Tuesday, July 3, 2018 8:27 PM
To: user@drill.apache.org
Subject: How to dynamically add months in Apache drill

Hello,

DATE_ADD function doesn't support a column as the interval argument in drill queries.

We have a requirement to pass a column as Interval Month to derive a forecasted date. Do you have any work around for this please?

The requirement is something like this -


Query that works
select
  a,
  DATE_ADD(date '2015-05-15', interval '1' month)
from
(
select '1' a, INSERTED_AT from dfs.data.bi_interaction limit 3
);

Query that doesn't work

select

  a,

  DATE_ADD(date '2015-05-15', interval a month)

from

(

 select '1' a, INSERTED_AT from dfs.data.bi_interaction limit 3

);



Thanks
Bharani