Re: Wrong results on repeated DATE_ADD

[Aman Sinha]

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.****