drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-4021) Cannot subract or add between two timestamps
Date Wed, 04 Nov 2015 15:37:27 GMT

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

Julian Hyde commented on DRILL-4021:
------------------------------------

For the record, and your table 7 notwithstanding, SQL-2011 only allows subtraction of a timestamp
from another using the '(ts1 - ts2) interval' syntax (see attached).

But it's fine that Drill goes beyond the standard as long as the semantics are clear. As Postgres
does.

> 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
>         Attachments: Screen Shot 2015-11-03 at 3.44.30 PM.png
>
>
> 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)

Mime
View raw message