You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2015/11/04 00:11:27 UTC
[jira] [Commented] (DRILL-4021) Cannot subract or add between two
timestamps
[ https://issues.apache.org/jira/browse/DRILL-4021?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14988403#comment-14988403 ]
Julian Hyde commented on DRILL-4021:
------------------------------------
I'll ask a rhetorical question: If you subtract two timestamps, what units would you expect the result to have? Milliseconds? Days?
Standard SQL says that if you subtract two date-time values you need to say what type of interval you can back (e.g. an interval in seconds). Thus you must write {code}t2 - t1 second{code} or {code}t2 - t1 month to year{code}.
Standard SQL does not allow you add two date-time values at all. What would you expect 2015-01-01 + 2015-01-01 to return? I suppose you could say 'a value twice as far from the 1970-01-01 epoch as 2015-01-01' but then you are assuming an epoch.
If you want to add to a date-time value, add an interval. {code}date '2015-01-01' + interval '2' year{code} should work.
So, this is not a bug.
> Cannot subract or add between two timestamps
> --------------------------------------------
>
> Key: DRILL-4021
> URL: https://issues.apache.org/jira/browse/DRILL-4021
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Data Types
> Reporter: Krystal
>
> The following subtraction between 2 now() function works:
> select now() - now()from voter_hive limit 1;
> +---------+
> | EXPR$0 |
> +---------+
> | PT0S |
> +---------+
>
> However, the following queries fail:
> select now() - create_time from voter_hive where voter_id=1;
> Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 26: Cannot apply '-' to arguments of type '<ANY> - <TIMESTAMP(0)>'. Supported form(s): '<NUMERIC> - <NUMERIC>'
> '<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
> '<DATETIME> - <DATETIME_INTERVAL>'
> select create_time - cast('1997-02-12 15:18:31.072' as timestamp) from voter_hive where voter_id=1;
> Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 65: Cannot apply '-' to arguments of type '<TIMESTAMP(0)> - <TIMESTAMP(0)>'. Supported form(s): '<NUMERIC> - <NUMERIC>'
> '<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
> '<DATETIME> - <DATETIME_INTERVAL>'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)