[
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)
