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