You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Rahul Raj <ra...@option3consulting.com> on 2017/03/09 13:59:25 UTC
Wrong results on repeated DATE_ADD
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.****
Re: Wrong results on repeated DATE_ADD
Posted by Rahul Raj <ra...@option3consulting.com>.
Create DRILL-5343
On Thu, Mar 9, 2017 at 9:08 PM, Aman Sinha <as...@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" <ra...@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.****
Re: Wrong results on repeated DATE_ADD
Posted by Aman Sinha <as...@mapr.com>.
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" <ra...@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.****