[
https://issues.apache.org/jira/browse/TRAFODION-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zhang.lei updated TRAFODION-3155:
---------------------------------
Description:
This feature is implemented on the basis of [<ANSI SQL-2003>8.13 overlaps
predicate, page
407|http://grigoras.perso.enseeiht.fr/ens/bd/5WD-02-Foundation-2003-09.pdf],
Here are some descriptions of it:
*Function*: Specify a test for an overlap between two datetime periods.
*Format*:
<overlaps predicate> ::= <overlaps predicate part 1> <overlaps predicate part 2>
<overlaps predicate part 1> ::= <row value predicand 1>
<overlaps predicate part 2> ::= OVERLAPS <row value predicand 2>
<row value predicand 1> ::= <row value predicand>
<row value predicand 2> ::= <row value predicand>
*Syntax Rules:*
1) The degrees of <row value predicand 1> and <row value predicand 2> shall
both be 2.
2) The declared types of the first field of <row value predicand 1> and the
first field of <row value predicand
2> shall both be datetime data types and these data types shall be comparable.
NOTE 195 — Two datetimes are comparable only if they have the same <primary
datetime field>s; see Subclause 4.6.2, “Datetimes”.
3) The declared type of the second field of each <row value predicand> shall
be a datetime data type or
INTERVAL.
Case:
a) If the declared type is INTERVAL, then the precision of the declared type
shall be such that the interval
can be added to the datetime data type of the first column of the <row value
predicand>.
b) If the declared type is a datetime data type, then it shall be comparable
with the datetime data type of
the first column of the <row value predicand>.
*Example:*
*--<comment> SELECT WHERE (date, date) OVERLAPS (date, date)*
>> select 1 from dual
+> where (date'2018-07-19', date'2018-08-19')
+> overlaps (date'2018-07-20', date'2018-08-20');
(EXPR)
------
1
— 1 row(s) selected.
*--<comment> SELECT WHERE(date, interval) OVERLAPS (date, date)*
>> select 1 from dual
+> where (date'2018-07-19', interval '01-02' year to month)
+> overlaps (date'2018-07-20', date'2018-08-20');
(EXPR)
------
1
— 1 row(s) selected.
>> select 1 from dual;
+> where (date'2018-07-19', -interval '03-02' year to month)
+> overlaps (date'2018-07-19', date'2018-08-18');
— 0 row(s) selected.
*>>-- <comment> SELECT WHERE (date, interval) OVERLAPS (date, interval)*
>> select 1 from dual;
+> where (date'2018-07-20', interval '1' month)
+> overlaps (date'2018-07-19', interval '01-02' year to month);
(EXPR)
------
1
— 1 row(s) selected.
*>>-- <comment> SELECT WHERE (time, time) OVERLAPS (time, time);*
>> select 1 from dual
+> where (time'01:02:03', time'02:03:04')
+> overlaps (time'03:04:05', time'02:01:01');
(EXPR)
------
1
--- 1 row(s) selected.
>>
*>>-- <comment> SELECT WHERE (time, interval) OVERLAPS (time, interval);*
>> select 0 from dual
+> where (time'03:04:06', interval '04:03:04' hour to second )
+> overlaps (time'03:04:05', -interval'02:01:01' hour to second);
*>>--<comment> SELECT WHERE (timestamp, timerstamp) OVERLAPS (timestamp,
timestamp);*
>> select 1 from btsel01
+> where (timestamp'2018-07-19 10:00:00', timestamp'2018-08-19 01:00:00')
+> overlaps (timestamp'2018-07-19 01:00:00', current_timestamp)
(EXPR)
------
1
--- 1 row(s) selected.
was:
This feature is implemented on the basis of [<ANSI SQL-2003>8.13 overlaps
predicate, page
407|http://grigoras.perso.enseeiht.fr/ens/bd/5WD-02-Foundation-2003-09.pdf],
Here are some descriptions of it:
*Function*: Specify a test for an overlap between two datetime periods.
*Format*:
<overlaps predicate> ::= <overlaps predicate part 1> <overlaps predicate part 2>
<overlaps predicate part 1> ::= <row value predicand 1>
<overlaps predicate part 2> ::= OVERLAPS <row value predicand 2>
<row value predicand 1> ::= <row value predicand>
<row value predicand 2> ::= <row value predicand>
*Syntax Rules:*
1) The degrees of <row value predicand 1> and <row value predicand 2> shall
both be 2.
2) The declared types of the first field of <row value predicand 1> and the
first field of <row value predicand
2> shall both be datetime data types and these data types shall be comparable.
NOTE 195 — Two datetimes are comparable only if they have the same <primary
datetime field>s; see Subclause 4.6.2, “Datetimes”.
3) The declared type of the second field of each <row value predicand> shall be
a datetime data type or
INTERVAL.
Case:
a) If the declared type is INTERVAL, then the precision of the declared type
shall be such that the interval
can be added to the datetime data type of the first column of the <row value
predicand>.
b) If the declared type is a datetime data type, then it shall be comparable
with the datetime data type of
the first column of the <row value predicand>.
*Example:*
*--<comment> SELECT WHERE (date, date) OVERLAPS (date, date)*
>> select 1 from dual
+> where (date'2018-07-19', date'2018-08-19')
+> overlaps (date'2018-07-20', date'2018-08-20');
(EXPR)
------
1
--- 1 row(s) selected.
*--<comment> SELECT WHERE(date, interval) OVERLAPS (date, date)*
>> select 1 from dual
+> where (date'2018-07-19', interval '01-02' year to month)
+> overlaps (date'2018-07-20', date'2018-08-20');
(EXPR)
------
1
--- 1 row(s) selected.
>> select 1 from dual;
+> where (date'2018-07-19', -interval '03-02' year to month)
+> overlaps (date'2018-07-19', date'2018-08-18');
--- 0 row(s) selected.
>>-- <comment> SELECT WHERE (date, interval) OVERLAPS (date, interval)
>> select 1 from dual;
+> where (date'2018-07-20', interval '1' month)
+> overlaps (date'2018-07-19', interval '01-02' year to month);
(EXPR)
------
1
--- 1 row(s) selected.
> New feature: Implementing the OVERLAPS predicate
> ------------------------------------------------
>
> Key: TRAFODION-3155
> URL: https://issues.apache.org/jira/browse/TRAFODION-3155
> Project: Apache Trafodion
> Issue Type: New Feature
> Reporter: zhang.lei
> Assignee: zhang.lei
> Priority: Major
>
> This feature is implemented on the basis of [<ANSI SQL-2003>8.13 overlaps
> predicate, page
> 407|http://grigoras.perso.enseeiht.fr/ens/bd/5WD-02-Foundation-2003-09.pdf],
> Here are some descriptions of it:
> *Function*: Specify a test for an overlap between two datetime periods.
> *Format*:
> <overlaps predicate> ::= <overlaps predicate part 1> <overlaps predicate part
> 2>
> <overlaps predicate part 1> ::= <row value predicand 1>
> <overlaps predicate part 2> ::= OVERLAPS <row value predicand 2>
> <row value predicand 1> ::= <row value predicand>
> <row value predicand 2> ::= <row value predicand>
> *Syntax Rules:*
> 1) The degrees of <row value predicand 1> and <row value predicand 2> shall
> both be 2.
> 2) The declared types of the first field of <row value predicand 1> and the
> first field of <row value predicand
> 2> shall both be datetime data types and these data types shall be
> comparable.
> NOTE 195 — Two datetimes are comparable only if they have the same <primary
> datetime field>s; see Subclause 4.6.2, “Datetimes”.
> 3) The declared type of the second field of each <row value predicand> shall
> be a datetime data type or
> INTERVAL.
> Case:
> a) If the declared type is INTERVAL, then the precision of the declared type
> shall be such that the interval
> can be added to the datetime data type of the first column of the <row value
> predicand>.
> b) If the declared type is a datetime data type, then it shall be comparable
> with the datetime data type of
> the first column of the <row value predicand>.
> *Example:*
> *--<comment> SELECT WHERE (date, date) OVERLAPS (date, date)*
> >> select 1 from dual
> +> where (date'2018-07-19', date'2018-08-19')
> +> overlaps (date'2018-07-20', date'2018-08-20');
> (EXPR)
> ------
> 1
> — 1 row(s) selected.
>
> *--<comment> SELECT WHERE(date, interval) OVERLAPS (date, date)*
> >> select 1 from dual
> +> where (date'2018-07-19', interval '01-02' year to month)
> +> overlaps (date'2018-07-20', date'2018-08-20');
> (EXPR)
> ------
> 1
> — 1 row(s) selected.
> >> select 1 from dual;
> +> where (date'2018-07-19', -interval '03-02' year to month)
> +> overlaps (date'2018-07-19', date'2018-08-18');
> — 0 row(s) selected.
> *>>-- <comment> SELECT WHERE (date, interval) OVERLAPS (date, interval)*
> >> select 1 from dual;
> +> where (date'2018-07-20', interval '1' month)
> +> overlaps (date'2018-07-19', interval '01-02' year to month);
> (EXPR)
> ------
> 1
> — 1 row(s) selected.
>
> *>>-- <comment> SELECT WHERE (time, time) OVERLAPS (time, time);*
> >> select 1 from dual
> +> where (time'01:02:03', time'02:03:04')
> +> overlaps (time'03:04:05', time'02:01:01');
> (EXPR)
> ------
> 1
> --- 1 row(s) selected.
> >>
> *>>-- <comment> SELECT WHERE (time, interval) OVERLAPS (time, interval);*
> >> select 0 from dual
> +> where (time'03:04:06', interval '04:03:04' hour to second )
> +> overlaps (time'03:04:05', -interval'02:01:01' hour to second);
>
> *>>--<comment> SELECT WHERE (timestamp, timerstamp) OVERLAPS (timestamp,
> timestamp);*
> >> select 1 from btsel01
> +> where (timestamp'2018-07-19 10:00:00', timestamp'2018-08-19 01:00:00')
> +> overlaps (timestamp'2018-07-19 01:00:00', current_timestamp)
> (EXPR)
> ------
> 1
> --- 1 row(s) selected.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
