[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2019-01-04 Thread Volodymyr Vysotskyi (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16734002#comment-16734002
 ] 

Volodymyr Vysotskyi commented on DRILL-3610:


Hi [~bbevens], docs look good, thanks!

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2019-01-03 Thread Bridget Bevens (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16733470#comment-16733470
 ] 

Bridget Bevens commented on DRILL-3610:
---

Hi [~vvysotskyi],

No worries; I've updated the queries for TIMESTAMPADD|DIFF with CAST function.
Hope they are okay now. 

Thanks,
Bridget


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-12-31 Thread Volodymyr Vysotskyi (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16731221#comment-16731221
 ] 

Volodymyr Vysotskyi commented on DRILL-3610:


Hi, [~bbevens],

Sorry for being picky, there is one more place to clarify: in the examples of 
usage for both {{TIMESTAMPADD}} and {{TIMESTAMPDIFF}} was used CSV table:
{code:sql}
SELECT TIMESTAMPADD(QUARTER, 1, COLUMNS[0]) q1 FROM dfs.`/quarter/dates.csv`;
{code}
But {{COLUMNS[0]}} has varchar type and {{TIMESTAMPADD}} requires 
date/time/timstamp types. Could you please add a cast to date for these 
examples?
{code:sql}
SELECT TIMESTAMPADD(QUARTER, 1, CAST(COLUMNS[0] as date)) q1 FROM 
dfs.`/quarter/dates.csv`;
{code}
Thanks in advance!

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-12-29 Thread Bridget Bevens (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16730852#comment-16730852
 ] 

Bridget Bevens commented on DRILL-3610:
---

Hi [~vvysotskyi],

I've updatd the descriptions and examples again. Hopefully they are accurate 
now, but if not, please let me know and I'll continue to work on them. :-) 

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

Thanks,
Bridget

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-12-28 Thread Volodymyr Vysotskyi (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16730094#comment-16730094
 ] 

Volodymyr Vysotskyi commented on DRILL-3610:


Hi, [~bbevens],

There are some inaccuracies in docs. 
 {{TIMESTAMPADD(time_unit,interval, keyword date_expression)}} is not quite 
right. {{TIMESTAMPADD}} require the last argument value (or a column) of date, 
time or timestamp type. In the case of literal, {{date '2003-01-02'}} gives us 
date literal, it is not connected with UDF itself.
The more general use case is
 {{TIMESTAMPADD(time_unit,interval, date_time_col)}}, where date_time_col has 
one the following types: date, time, or timestamp. The same for 
{{TIMESTAMPDIFF}} function.

Could you please correct docs to clarify that?

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-12-27 Thread Bridget Bevens (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16730078#comment-16730078
 ] 

Bridget Bevens commented on DRILL-3610:
---

Hi [~vvysotskyi],

Thanks for reviewing. I updated the pages based on your feedback; hopefully 
they are correct now. If not, let me know and I'll change them again.

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

Thanks,
Bridget

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-12-27 Thread Volodymyr Vysotskyi (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16729429#comment-16729429
 ] 

Volodymyr Vysotskyi commented on DRILL-3610:


Hi, [~bbevens], thanks for adding the docs!
There is a mistake in the queries for {{TIMESTAMPADD}}: this UDF should accept 
not varchar values as the last argument, but date, time or timestamp values.

So the next query works correctly:
{code:sql}
SELECT TIMESTAMPADD(MINUTE,1,date '2003-01-02');
{code}
{noformat}
++
| EXPR$0 |
++
| 2003-01-02 00:01:00.0  |
++
1 row selected (1.066 seconds)
{noformat}
Could you please modify docs to reflect that?

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-complete, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-12-26 Thread Bridget Bevens (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16729305#comment-16729305
 ] 

Bridget Bevens commented on DRILL-3610:
---

Hi [~vvysotskyi]

 On this page: 
https://drill.apache.org/docs/date-time-functions-and-arithmetic/    

I added the following two sections:   

* 
[https://drill.apache.org/docs/date-time-functions-and-arithmetic/#timestampadd]
* 
[https://drill.apache.org/docs/date-time-functions-and-arithmetic/#timestampdiff]

For timestampadd, I did not include hour, minute, second, etc. time units 
because they did not work for me, as shown:

SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

Error: SYSTEM ERROR: AssertionError: Conversion to relational algebra failed to 
preserve datatypes:

validated type:

RecordType(TIMESTAMP(3) NOT NULL EXPR$0) NOT NULL

converted type:

RecordType(TIMESTAMP(0) NOT NULL EXPR$0) NOT NULL

rel:

LogicalProject(EXPR$0=[+('2003-01-02', *(6, 1))])

  LogicalValues(tuples=[[\{ 0 }]])

Please, refer to logs for more information.

 

Please review and let me know if I need to make any changes to the doc.

Thanks,
 Bridget

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting, ready-to-commit
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16682170#comment-16682170
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

ilooner closed pull request #1528: DRILL-3610: Add TIMESTAMPADD and 
TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528
 
 
   

This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:

As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):

diff --git a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd 
b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
index 96e16070aa5..bfa44c8df09 100644
--- a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
+++ b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
@@ -18,6 +18,7 @@
 {intervals: ["Interval", "IntervalDay", "IntervalYear", "Int", "BigInt"] },
 {truncInputTypes: ["Date", "TimeStamp", "Time", "Interval", "IntervalDay", 
"IntervalYear"] },
 {truncUnits : ["Second", "Minute", "Hour", "Day", "Month", "Year", "Week", 
"Quarter", "Decade", "Century", "Millennium" ] },
+{timestampDiffUnits : ["Nanosecond", "Microsecond", "Second", "Minute", 
"Hour", "Day", "Month", "Year", "Week", "Quarter"] },
 
 {
 varCharToDate: [
diff --git 
a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
 
b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
index 66e754c3606..de2051a8acd 100644
--- 
a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
+++ 
b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
@@ -129,20 +129,22 @@ public void eval() {
 }
 }
 
-@SuppressWarnings("unused")
-@FunctionTemplate(names = {"divide", "div"}, scope = 
FunctionTemplate.FunctionScope.SIMPLE, nulls=NullHandling.NULL_IF_NULL)
-public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
+  @SuppressWarnings("unused")
+  @FunctionTemplate(names = {"divide", "div"<#if numerictype == "Int">, 
"/int"},
+scope = FunctionTemplate.FunctionScope.SIMPLE,
+nulls = NullHandling.NULL_IF_NULL)
+  public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
 @Param ${intervaltype}Holder left;
 @Param ${numerictype}Holder right;
 @Output IntervalHolder out;
 
-public void setup() {
-}
+public void setup() {
+}
 
-public void eval() {
-<@intervalNumericArithmeticBlock left="left" right="right" temp = 
"temp" op = "/" out = "out" intervaltype=intervaltype />
-}
+public void eval() {
+  <@intervalNumericArithmeticBlock left="left" right="right" temp = "temp" 
op = "/" out = "out" intervaltype=intervaltype />
 }
+  }
 }
 
-
\ No newline at end of file
+
diff --git 
a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/TimestampDiff.java
 
b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/TimestampDiff.java
new file mode 100644
index 000..54232e26825
--- /dev/null
+++ 
b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/TimestampDiff.java
@@ -0,0 +1,109 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+<@pp.dropOutputFile />
+<#assign className="GTimestampDiff"/>
+
+<@pp.changeOutputFile 
name="/org/apache/drill/exec/expr/fn/impl/${className}.java"/>
+
+<#include "/@includes/license.ftl"/>
+
+package org.apache.drill.exec.expr.fn.impl;
+
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+import org.apache.drill.exec.expr.annotations.FunctionTemplate;
+import org.apache.drill.exec.expr.annotations.FunctionTemplate.NullHandling;
+import org.apache.drill.exec.expr.annotations.Output;
+import org.apache.drill.exec.expr.annotations.Workspace;
+import org.apache.drill.exec.expr.annotations.Param;
+impor

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681798#comment-16681798
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on issue #1528: DRILL-3610: Add TIMESTAMPADD and 
TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#issuecomment-437449830
 
 
   +1 LGTM


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681761#comment-16681761
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232337486
 
 

 ##
 File path: 
exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
 ##
 @@ -129,20 +129,22 @@ public void eval() {
 }
 }
 
-@SuppressWarnings("unused")
-@FunctionTemplate(names = {"divide", "div"}, scope = 
FunctionTemplate.FunctionScope.SIMPLE, nulls=NullHandling.NULL_IF_NULL)
-public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
+  @SuppressWarnings("unused")
+  @FunctionTemplate(names = {"divide", "div"<#if numerictype == "Int">, 
"/int"},
+scope = FunctionTemplate.FunctionScope.SIMPLE,
+nulls = NullHandling.NULL_IF_NULL)
 
 Review comment:
   `FunctionTemplate` has `isInternal` field for these purposes, but since 
`/int` is used with other non-internal function names, we cant mark it only as 
internal without splitting it to the separate UDF.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681759#comment-16681759
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232337818
 
 

 ##
 File path: 
exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
 ##
 @@ -555,6 +557,60 @@ public RelDataType inferReturnType(SqlOperatorBinding 
opBinding) {
 }
   }
 
+  private static class DrillTimestampAddTypeInference implements 
SqlReturnTypeInference {
+private static final SqlReturnTypeInference INSTANCE = new 
DrillTimestampAddTypeInference();
+
+@Override
+public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
+  RelDataTypeFactory factory = opBinding.getTypeFactory();
+  // operands count ond order is checked at parsing stage
+  RelDataType inputType = opBinding.getOperandType(2);
+  boolean isNullable = inputType.isNullable() || 
opBinding.getOperandType(1).isNullable();
+
+  SqlTypeName inputTypeName = inputType.getSqlTypeName();
+
+  TimeUnit qualifier = ((SqlLiteral) ((SqlCallBinding) 
opBinding).operand(0)).getValueAs(TimeUnit.class);
+
+  SqlTypeName sqlTypeName;
+
+  // follow up with type inference of reduced expression
+  switch (qualifier) {
+case DAY:
+case WEEK:
+case MONTH:
+case QUARTER:
+case YEAR:
+case NANOSECOND:  // NANOSECOND is not supported by Calcite 
SqlTimestampAddFunction.
+  // Once it is fixed, NANOSECOND should be moved to 
the group below.
+  sqlTypeName = inputTypeName;
+  break;
+case MICROSECOND:
+case MILLISECOND:
+  // for MICROSECOND and MILLISECOND should be specified precision
 
 Review comment:
   Thanks, replaced.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681758#comment-16681758
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232334614
 
 

 ##
 File path: exec/java-exec/src/main/codegen/data/MathFunctionTypes.tdd
 ##
 @@ -67,6 +67,19 @@
   {input1: "UInt4", input2: "UInt4", outputType: "UInt4", castType: 
"int"},
   {input1: "UInt8", input2: "UInt8", outputType: "UInt8", castType: 
"long"}
 ]
- }
+},
+{className: "DivideInt", funcName: "/int", op: "/", types: [
 
 Review comment:
   Yes, these UDFs were used in one of the previous version of the fix, so now 
they can be removed. Thanks for pointing this.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681760#comment-16681760
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232337760
 
 

 ##
 File path: 
exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConvertletTable.java
 ##
 @@ -61,13 +69,36 @@
 }
   };
 
+  // Custom convertlet to avoid rewriting TIMESTAMP_DIFF by Calcite.
 
 Review comment:
   Thanks, added.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681762#comment-16681762
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232338073
 
 

 ##
 File path: 
exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateAddFunctions.java
 ##
 @@ -30,6 +35,29 @@
 @Category({UnlikelyTest.class, SqlFunctionTest.class})
 public class TestDateAddFunctions extends BaseTestQuery {
 
 Review comment:
   Agree, moved them to the separate class.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681757#comment-16681757
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232337509
 
 

 ##
 File path: 
exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
 ##
 @@ -129,20 +129,22 @@ public void eval() {
 }
 }
 
-@SuppressWarnings("unused")
-@FunctionTemplate(names = {"divide", "div"}, scope = 
FunctionTemplate.FunctionScope.SIMPLE, nulls=NullHandling.NULL_IF_NULL)
-public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
+  @SuppressWarnings("unused")
+  @FunctionTemplate(names = {"divide", "div"<#if numerictype == "Int">, 
"/int"},
+scope = FunctionTemplate.FunctionScope.SIMPLE,
+nulls = NullHandling.NULL_IF_NULL)
+  public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
 @Param ${intervaltype}Holder left;
 @Param ${numerictype}Holder right;
 @Output IntervalHolder out;
 
-public void setup() {
-}
+public void setup() {
+}
 
-public void eval() {
-<@intervalNumericArithmeticBlock left="left" right="right" temp = 
"temp" op = "/" out = "out" intervaltype=intervaltype />
-}
+public void eval() {
+  <@intervalNumericArithmeticBlock left="left" right="right" temp = "temp" 
op = "/" out = "out" intervaltype=intervaltype />
 }
+  }
 }
 
 
 
 Review comment:
   done


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681669#comment-16681669
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r231945399
 
 

 ##
 File path: exec/java-exec/src/main/codegen/data/MathFunctionTypes.tdd
 ##
 @@ -67,6 +67,19 @@
   {input1: "UInt4", input2: "UInt4", outputType: "UInt4", castType: 
"int"},
   {input1: "UInt8", input2: "UInt8", outputType: "UInt8", castType: 
"long"}
 ]
- }
+},
+{className: "DivideInt", funcName: "/int", op: "/", types: [
+  {input1: "Int", input2: "Int", outputType: "Int", castType: "int"},
+  {input1: "BigInt", input2: "BigInt", outputType: "Int", castType: 
"int"},
+  {input1: "Float4", input2: "Float4", outputType: "Int", castType: 
"int"},
+  {input1: "Float8", input2: "Float8", outputType: "Int", castType: 
"int"},
+  {input1: "SmallInt", input2: "SmallInt", outputType: "Int", 
castType: "int"},
+  {input1: "TinyInt", input2: "TinyInt", outputType: "Int", castType: 
"int"},
+  {input1: "UInt1", input2: "UInt1", outputType: "Int", castType: 
"int"},
+  {input1: "UInt2", input2: "UInt2", outputType: "Int", castType: 
"int"},
+  {input1: "UInt4", input2: "UInt4", outputType: "Int", castType: 
"int"},
+  {input1: "UInt8", input2: "UInt8", outputType: "Int", castType: 
"int"}
+]
+}
   ]
 }
 
 Review comment:
   add new line


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681671#comment-16681671
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232231016
 
 

 ##
 File path: 
exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
 ##
 @@ -555,6 +557,60 @@ public RelDataType inferReturnType(SqlOperatorBinding 
opBinding) {
 }
   }
 
+  private static class DrillTimestampAddTypeInference implements 
SqlReturnTypeInference {
+private static final SqlReturnTypeInference INSTANCE = new 
DrillTimestampAddTypeInference();
+
+@Override
+public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
+  RelDataTypeFactory factory = opBinding.getTypeFactory();
+  // operands count ond order is checked at parsing stage
+  RelDataType inputType = opBinding.getOperandType(2);
+  boolean isNullable = inputType.isNullable() || 
opBinding.getOperandType(1).isNullable();
+
+  SqlTypeName inputTypeName = inputType.getSqlTypeName();
+
+  TimeUnit qualifier = ((SqlLiteral) ((SqlCallBinding) 
opBinding).operand(0)).getValueAs(TimeUnit.class);
+
+  SqlTypeName sqlTypeName;
+
+  // follow up with type inference of reduced expression
+  switch (qualifier) {
+case DAY:
+case WEEK:
+case MONTH:
+case QUARTER:
+case YEAR:
+case NANOSECOND:  // NANOSECOND is not supported by Calcite 
SqlTimestampAddFunction.
+  // Once it is fixed, NANOSECOND should be moved to 
the group below.
+  sqlTypeName = inputTypeName;
+  break;
+case MICROSECOND:
+case MILLISECOND:
+  // for MICROSECOND and MILLISECOND should be specified precision
 
 Review comment:
   replace with 
   
   > precision should be specified for MICROSECOND and MILLISECOND


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681670#comment-16681670
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232034213
 
 

 ##
 File path: 
exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateAddFunctions.java
 ##
 @@ -30,6 +35,29 @@
 @Category({UnlikelyTest.class, SqlFunctionTest.class})
 public class TestDateAddFunctions extends BaseTestQuery {
 
 Review comment:
   Looks like `TIMESTAMPDIFF` and `TIMESTAMPADD` differ from `DATE_ADD`, 
therefore please create a separate class for the new unit tests.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681668#comment-16681668
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232229127
 
 

 ##
 File path: 
exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConvertletTable.java
 ##
 @@ -61,13 +69,36 @@
 }
   };
 
+  // Custom convertlet to avoid rewriting TIMESTAMP_DIFF by Calcite.
 
 Review comment:
   please add the small note about the reason why Calcite's 
`TimestampDiffConvertlet` is not suitable for Drill (similar to desc in PR).


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681672#comment-16681672
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232021092
 
 

 ##
 File path: 
exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
 ##
 @@ -129,20 +129,22 @@ public void eval() {
 }
 }
 
-@SuppressWarnings("unused")
-@FunctionTemplate(names = {"divide", "div"}, scope = 
FunctionTemplate.FunctionScope.SIMPLE, nulls=NullHandling.NULL_IF_NULL)
-public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
+  @SuppressWarnings("unused")
+  @FunctionTemplate(names = {"divide", "div"<#if numerictype == "Int">, 
"/int"},
+scope = FunctionTemplate.FunctionScope.SIMPLE,
+nulls = NullHandling.NULL_IF_NULL)
 
 Review comment:
   Is it possible to specify somewhere that `/int` function (unlike `divide` 
and `div`) is for internal use?


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681673#comment-16681673
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r231976137
 
 

 ##
 File path: 
exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
 ##
 @@ -129,20 +129,22 @@ public void eval() {
 }
 }
 
-@SuppressWarnings("unused")
-@FunctionTemplate(names = {"divide", "div"}, scope = 
FunctionTemplate.FunctionScope.SIMPLE, nulls=NullHandling.NULL_IF_NULL)
-public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
+  @SuppressWarnings("unused")
+  @FunctionTemplate(names = {"divide", "div"<#if numerictype == "Int">, 
"/int"},
+scope = FunctionTemplate.FunctionScope.SIMPLE,
+nulls = NullHandling.NULL_IF_NULL)
+  public static class ${intervaltype}${numerictype}DivideFunction implements 
DrillSimpleFunc {
 @Param ${intervaltype}Holder left;
 @Param ${numerictype}Holder right;
 @Output IntervalHolder out;
 
-public void setup() {
-}
+public void setup() {
+}
 
-public void eval() {
-<@intervalNumericArithmeticBlock left="left" right="right" temp = 
"temp" op = "/" out = "out" intervaltype=intervaltype />
-}
+public void eval() {
+  <@intervalNumericArithmeticBlock left="left" right="right" temp = "temp" 
op = "/" out = "out" intervaltype=intervaltype />
 }
+  }
 }
 
 
 
 Review comment:
   new line


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-09 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681674#comment-16681674
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on a change in pull request #1528: DRILL-3610: Add 
TIMESTAMPADD and TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528#discussion_r232298242
 
 

 ##
 File path: exec/java-exec/src/main/codegen/data/MathFunctionTypes.tdd
 ##
 @@ -67,6 +67,19 @@
   {input1: "UInt4", input2: "UInt4", outputType: "UInt4", castType: 
"int"},
   {input1: "UInt8", input2: "UInt8", outputType: "UInt8", castType: 
"long"}
 ]
- }
+},
+{className: "DivideInt", funcName: "/int", op: "/", types: [
 
 Review comment:
   Looks like these functions are nowhere used and can be removed


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-08 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16679515#comment-16679515
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi opened a new pull request #1527: DRILL-3610 & DRILL-4456 Fix 
TIMESTAMPADD, TIMESTAMPDIFF and Hive TRANSLATE functions
URL: https://github.com/apache/drill/pull/1527
 
 
   - In the first commit was added renaming of Hive `TRANSLATE` function to 
`TRANSLATE3` during function registration.
   
   - In the second commit was added type inference for `TIMESTAMPADD` function 
to avoid failures for the cases when function types are checked for expressions 
before and after rewriting.
   - Added custom convertlet for `TIMESTAMPDIFF` function to avoid rewriting it 
by Calcite, since Drill does not support `Reinterpret` function and does not 
handle all Calcite interval representations correctly.
   - Added UDFs for `TIMESTAMPDIFF` for all supported time units. The mechanism 
is similar to the existing `EXTRACT` function: when Calcite `RexCall` is 
converted to the Drill `LogicalExpression`, a function name is changed to 
`timestampdiffSecond` or similar one depending on the specified time unit.
   
   For problem descriptions please see 
[DRILL-3610](https://issues.apache.org/jira/browse/DRILL-3610) and 
[DRILL-4456](https://issues.apache.org/jira/browse/DRILL-4456).
   


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-08 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16679678#comment-16679678
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka commented on issue #1527: DRILL-3610 & DRILL-4456 Fix TIMESTAMPADD, 
TIMESTAMPDIFF and Hive TRANSLATE functions
URL: https://github.com/apache/drill/pull/1527#issuecomment-436976287
 
 
   @vvysotskyi The PR with two commits make sense if that tickets are 
interdependent.
   Looks like this case is different. So please split current PR to the two 
different ones.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-08 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16679523#comment-16679523
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

arina-ielchiieva commented on issue #1527: DRILL-3610 & DRILL-4456 Fix 
TIMESTAMPADD, TIMESTAMPDIFF and Hive TRANSLATE functions
URL: https://github.com/apache/drill/pull/1527#issuecomment-436938037
 
 
   @vdiravka could you please review.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-08 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16679679#comment-16679679
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vdiravka edited a comment on issue #1527: DRILL-3610 & DRILL-4456 Fix 
TIMESTAMPADD, TIMESTAMPDIFF and Hive TRANSLATE functions
URL: https://github.com/apache/drill/pull/1527#issuecomment-436976287
 
 
   @vvysotskyi The PR with two commits makes sense if that tickets are 
interdependent.
   Looks like this case is different. So please split current PR to the two 
different ones.


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-08 Thread ASF GitHub Bot (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16679683#comment-16679683
 ] 

ASF GitHub Bot commented on DRILL-3610:
---

vvysotskyi opened a new pull request #1528: DRILL-3610: Fix TIMESTAMPADD and 
TIMESTAMPDIFF functions
URL: https://github.com/apache/drill/pull/1528
 
 
   - Added type inference for TIMESTAMPADD function to avoid failures for the 
cases when function types are checked for expressions before and after 
rewriting.
   
   - Added custom convertlet for TIMESTAMPDIFF function to avoid rewriting it 
by Calcite, since Drill does not support Reinterpret function and does not 
handle all Calcite interval representations correctly.
   
   - Added UDFs for TIMESTAMPDIFF for all supported time units. The mechanism 
is similar to the existing EXTRACT function: when Calcite RexCall is converted 
to the Drill LogicalExpression, a function name is changed to 
timestampdiffSecond or similar one depending on the specified time unit.
   
   For problem description please see 
[DRILL-3610](https://issues.apache.org/jira/browse/DRILL-3610).


This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-05 Thread Volodymyr Vysotskyi (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16675167#comment-16675167
 ] 

Volodymyr Vysotskyi commented on DRILL-3610:


As a workaround for the problem above, TIMESTAMPDIFF may be rewritten as CASE 
call, which calls EXTRACT for required time units for both dates, subtracts 
them, and calls EXTRACT for the next time unit to check, whether the previous 
result shouldn't be decreased. For example, for the statement {{SELECT 
TIMESTAMPDIFF(YEAR,'2009-04-18','2010-05-18')}} should be called 
{{EXTRACT(YEAR,'2010-05-18')-EXTRACT(YEAR,'2009-04-18')}}, and also called 
{{EXTRACT(MONTH,'2010-05-18')-EXTRACT(MONTH,'2009-04-18')}} to decrease the 
first value by one for the case when the last statement is less than zero.

But it may cause generation of large statements. For the query, specified 
above, it will generate the next statements
{code:sql}
+(CASE(AND(>(-(EXTRACT(FLAG(YEAR), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(YEAR), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(MONTH), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MONTH), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(DAY), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(DAY), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(HOUR), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(HOUR), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(MINUTE), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MINUTE), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(SECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(SECOND), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(MILLISECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MILLISECOND), 2019-03-24 17:40:52.123)), 0), 
<(+(CASE(AND(>(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
<(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), -1, 
AND(<(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
>(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), 1, 0), 
-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123))), 0)), -1, 
AND(<(-(EXTRACT(FLAG(MILLISECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MILLISECOND), 2019-03-24 17:40:52.123)), 0), 
>(+(CASE(AND(>(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
<(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), -1, 
AND(<(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
>(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), 1, 0), 
-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123))), 0)), 1, 0), 
-(EXTRACT(FLAG(MILLISECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MILLISECOND), 2019-03-24 17:40:52.123))), 0)), -1, 
AND(<(-(EXTRACT(FLAG(SECOND), 2018-03-25 17:40:52.123), EXTRACT(FLAG(SECOND), 
2019-03-24 17:40:52.123)), 0), >(+(CASE(AND(>(-(EXTRACT(FLAG(MILLISECOND), 
2018-03-25 17:40:52.123), EXTRACT(FLAG(MILLISECOND), 2019-03-24 17:40:52.123)), 
0), <(+(CASE(AND(>(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
<(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), -1, 
AND(<(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
>(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), 1, 0), 
-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123))), 0)), -1, 
AND(<(-(EXTRACT(FLAG(MILLISECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MILLISECOND), 2019-03-24 17:40:52.123)), 0), 
>(+(CASE(AND(>(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
<(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), -1, 
AND(<(-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123)), 0), 
>(-(EXTRACT(FLAG(NANOSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(NANOSECOND), 2019-03-24 17:40:52.123)), 0)), 1, 0), 
-(EXTRACT(FLAG(MICROSECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MICROSECOND), 2019-03-24 17:40:52.123))), 0)), 1, 0), 
-(EXTRACT(FLAG(MILLISECOND), 2018-03-25 17:40:52.123), 
EXTRACT(FLAG(MILLISECOND), 2019-03-24 17:40

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2018-11-05 Thread Volodymyr Vysotskyi (JIRA) 


[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16674958#comment-16674958
 ] 

Volodymyr Vysotskyi commented on DRILL-3610:


{{TIMESTAMPDIFF}} may be rewritten using {{EXTRACT}} function. But simply 
extracting the required qualifier from the {{INTERVAL}} which corresponds to 
the dates difference may cause unexpected results, since Interval cannot be 
normalized: days overflow cannot be converted to months, etc.

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Volodymyr Vysotskyi
>Priority: Major
>  Labels: doc-impacting
> Fix For: 1.15.0
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2016-03-10 Thread Arina Ielchiieva (JIRA) 

[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15189656#comment-15189656
 ] 

Arina Ielchiieva commented on DRILL-3610:
-

Pending on merge with Calcite latest version.

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Arina Ielchiieva
> Fix For: Future
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2016-02-25 Thread Oscar Morante (JIRA) 

[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15167542#comment-15167542
 ] 

Oscar Morante commented on DRILL-3610:
--

Tableau seems to use this for some aggregated extracts.

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Arina Ielchiieva
> Fix For: Future
>
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2015-08-06 Thread Andries Engelbrecht (JIRA) 

[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14660188#comment-14660188
 ] 

Andries Engelbrecht commented on DRILL-3610:


Potentially can be used, but still leaves a gap in terms of syntax used for a 
common DATETIME function, where ADD and DIFF will be very different.



> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Mehant Baid
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2015-08-05 Thread Daniel Barclay (Drill) (JIRA) 

[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14658884#comment-14658884
 ] 

Daniel Barclay (Drill) commented on DRILL-3610:
---

> Date_ADD can be substituted for TIMESTAMPADD, but lacks QUARTER interval 
> commonly used in financial analysis.

Using {{date_expr + INTERVAL '3' MONTHS}} or {{date_expr + (number_of_quarters 
* '3' MONTHS)}} doesn't cover that well enough?

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Mehant Baid
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2015-08-05 Thread Andries Engelbrecht (JIRA) 

[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14658856#comment-14658856
 ] 

Andries Engelbrecht commented on DRILL-3610:


TIMESTAMPDIFF(SQL_TSI_) returns the difference based on the 
specified interval between the 2 supplied timestamps, where interval can be 
second, minute, hour, day, week, month, quarter or year as an integer.

EXTRACT/Date_Part and Timestamp functions can be used to substitute, but 
requires more extensive SQL to achieve the same operation. Can be very 
cumbersome in queries with multiple of these operations, also with machine 
generated queries.

Date_ADD can be substituted for TIMESTAMPADD, but lacks QUARTER interval 
commonly used in financial analysis.

> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Mehant Baid
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-3610) TimestampAdd/Diff (SQL_TSI_) functions

2015-08-05 Thread Daniel Barclay (Drill) (JIRA) 

[ 
https://issues.apache.org/jira/browse/DRILL-3610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14658554#comment-14658554
 ] 

Daniel Barclay (Drill) commented on DRILL-3610:
---

Standard SQL already defines addition and subtraction operations on valid 
combinations of timestamps (and other datetime) types and intervals (see 
ISO/IEC 9075-2:2011(E) section 4.6.4).

Do the functions requested above provide functionality that the standard 
operations do not provide?



> TimestampAdd/Diff (SQL_TSI_) functions
> --
>
> Key: DRILL-3610
> URL: https://issues.apache.org/jira/browse/DRILL-3610
> Project: Apache Drill
>  Issue Type: Improvement
>  Components: Functions - Drill
>Reporter: Andries Engelbrecht
>Assignee: Mehant Baid
>
> Add TimestampAdd and TimestampDiff (SQL_TSI) functions for year, quarter, 
> month, week, day, hour, minute, second.
> Examples
> SELECT CAST(TIMESTAMPADD(SQL_TSI_QUARTER,1,Date('2013-03-31'), SQL_DATE) AS 
> `column_quarter`
> FROM `table_in`
> HAVING (COUNT(1) > 0)
> SELECT `table_in`.`datetime` AS `column1`,
>   `table`.`Key` AS `column_Key`,
>   TIMESTAMPDIFF(SQL_TSI_MINUTE,to_timestamp('2004-07-04', 
> '-MM-dd'),`table_in`.`datetime`) AS `sum_datediff_minute`
> FROM `calcs`



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)