Create DRILL-5343 On Thu, Mar 9, 2017 at 9:08 PM, Aman Sinha <asi...@mapr.com> wrote:
> Thanks for detecting this. Odd that it works for Day but not for minute > intervals. Can you pls file a JIRA ? > > -Aman > > On 3/9/17, 5:59 AM, "Rahul Raj" <rahul....@option3consulting.com> wrote: > > Hi, > > On Drill 1.9, DATE_ADD(DATE_ADD ...) results in the inner most value > getting added up N times. Its seen on MINUTE/SECOND/HOUR interval > values, > It works fine on DAY interval > > See the results below; I have trimmed the sqlline results for brevity. > > > SELECT DATE_ADD(TIME '12:23:34',INTERVAL '1' minute) from (values(1)); > +-----------+ > | 12:24:34 | > +-----------+ > > SELECT DATE_ADD(TIME '12:23:34',INTERVAL '5' minute) from (values(1)); > +-----------+ > | 12:28:34 | > +-----------+ > > SELECT DATE_ADD(DATE_ADD(TIME '12:23:34',INTERVAL '5' minute),INTERVAL > '1' > minute) from (values(1)); > +-----------+ > | 12:33:34 | > +-----------+ > > SELECT DATE_ADD(DATE_ADD(DATE_ADD(TIME '12:23:34',INTERVAL '5' > minute),INTERVAL '1' minute),INTERVAL '2' minute) from (values(1)); > +-----------+ > | 12:38:34 | > +-----------+ > > > SELECT DATE_ADD(DATE_ADD(DATE '2008-2-23',INTERVAL '5' > minute),INTERVAL '2' > minute) from (values(1)); > +------------------------+ > | 2008-02-23 00:10:00.0 | > +------------------------+ > > SELECT DATE_ADD(DATE_ADD(DATE '2008-2-23',INTERVAL '5' > second),INTERVAL '2' > second) from (values(1)); > +------------------------+ > | 2008-02-23 00:00:10.0 | > +------------------------+ > > SELECT DATE_ADD(DATE_ADD(DATE '2008-2-23',INTERVAL '5' hour),INTERVAL > '2' > hour) from (values(1)); > +------------------------+ > | 2008-02-23 10:00:00.0 | > +------------------------+ > > > DAY interval works fine. > > SELECT DATE_ADD(DATE '2008-2-23',INTERVAL '5' day) from (values(1)); > +------------------------+ > | 2008-02-28 00:00:00.0 | > +------------------------+ > > SELECT DATE_ADD(DATE_ADD(DATE '2008-2-23',INTERVAL '5' day),INTERVAL > '1' > day) from (values(1)); > +------------------------+ > | 2008-02-29 00:00:00.0 | > +------------------------+ > > SELECT DATE_ADD(DATE_ADD(DATE '2008-2-23',INTERVAL '5' day),INTERVAL > '2' > day) from (values(1)); > +------------------------+ > | 2008-03-01 00:00:00.0 | > +------------------------+ > > > Regards, > Rahul > > -- > **** This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom it is > addressed. If you are not the named addressee then you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately and delete this e-mail from your system.**** > > > -- **** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. If you are not the named addressee then you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and delete this e-mail from your system.****
