[ https://issues.apache.org/jira/browse/DRILL-4116?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16055264#comment-16055264 ]
Vitalii Diravka commented on DRILL-4116: ---------------------------------------- datediff() is a [hive build-in function|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF], which can be used in [drill|https://drill.apache.org/docs/why-drill/#8.-user-defined-functions-(udfs)-for-drill-and-hive]. It works properly with string parameters: {code} 0: jdbc:drill:zk=local> select datediff('1996-03-01', '1997-02-10 17:32:00.0'), TIMEOFDAY() from (VALUES(1)); +---------+----------------------------------+ | EXPR$0 | EXPR$1 | +---------+----------------------------------+ | -346 | 2017-06-20 14:04:56.692 Etc/UCT | +---------+----------------------------------+ {code} {code} 0: jdbc:drill:zk=local> select datediff('1996-03-01', '1997-02-10 17:32:00.0'), TIMEOFDAY() from (VALUES(1)); +---------+----------------------------------------------+ | EXPR$0 | EXPR$1 | +---------+----------------------------------------------+ | -346 | 2017-06-20 07:05:42.372 America/Los_Angeles | +---------+----------------------------------------------+ {code} But the issue with date, timestamp parameters and described here [DRILL-5002|https://issues.apache.org/jira/browse/DRILL-5002] (this case is added to that jira). The workaround is to use drill's "date_diff" build-in function and convert the returned interval to a number: {code} 0: jdbc:drill:zk=local> select to_number(date_diff(date '1996-03-01', timestamp '1997-02-10 17:32:00.0'), '#'), TIMEOFDAY() from (VALUES(1)); +---------+----------------------------------------------+ | EXPR$0 | EXPR$1 | +---------+----------------------------------------------+ | -346.0 | 2017-06-20 07:10:14.839 America/Los_Angeles | +---------+----------------------------------------------+ {code} {code} 0: jdbc:drill:zk=local> select to_number(date_diff(date '1996-03-01', timestamp '1997-02-10 17:32:00.0'), '#'), TIMEOFDAY() from (VALUES(1)); +---------+----------------------------------+ | EXPR$0 | EXPR$1 | +---------+----------------------------------+ | -346.0 | 2017-06-20 14:11:07.658 Etc/UCT | +---------+----------------------------------+ {code} > Inconsistent results with datetime functions on different machines > ------------------------------------------------------------------ > > Key: DRILL-4116 > URL: https://issues.apache.org/jira/browse/DRILL-4116 > Project: Apache Drill > Issue Type: Bug > Components: Functions - Drill > Affects Versions: 1.3.0 > Reporter: Rahul Challapalli > Assignee: Vitalii Diravka > Priority: Critical > > git.commit.id.abbrev=a6a0fc3 > The below query yields different results on different machines > System 1 : > {code} > 0: jdbc:drill:zk=10.10.100.190:5181> select datediff(date '1996-03-01', > timestamp '1997-02-10 17:32:00.0') from cp.`tpch/lineitem.parquet` limit 1; > +---------+ > | EXPR$0 | > +---------+ > | -346 | > +---------+ > 1 row selected (1.57 seconds) > {code} > System 2 : > {code} > 0: jdbc:drill:drillbit=10.10.88.193> select datediff(date '1996-03-01', > timestamp '1997-02-10 17:32:00.0') from cp.`tpch/lineitem.parquet` limit 1; > +---------+ > | EXPR$0 | > +---------+ > | -347 | > +---------+ > 1 row selected (1.239 seconds) > {code} -- This message was sent by Atlassian JIRA (v6.4.14#64029)