[SPARK-30070] ANSI-F053: Overlaps datetimes predicate support

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Kent Yao 2
• Created: 28/Nov/19 12:29
• Updated: 29/Mar/20 02:13

Description

 * The operator `OVERLAPS` determines whether or not two chronological periods overlap in time. A
 * chronological period is specified by a pair of datetimes (starting and ending).
 *
 * If the length of the period is greater than 0, then the period consists of all points of time
 * greater than or equal to the lower endpoint, and less than the upper endpoint,
 * a.k.a [lower, upper).
 *
 * If the length of the period is equal to 0, then the period consists of a single point in time,
 * the lower endpoint, a.k.a [lower, lower].
 *
 * Two periods overlap if they have at least one point in common.

PostgreSQL

postgres=# select (cast(a as timestamp), cast(b as timestamp)) overlaps (cast(c as timestamp), cast(d as timestamp)) from (values
 ('2011-11-11', '2011-11-11', '2011-11-11', '2011-11-11'),
 ('2011-11-10', '2011-11-11', '2011-11-11', '2011-11-12'),
 ('2011-11-11', '2011-11-10', '2011-11-11', '2011-11-12'),
 ('2011-11-11', '2011-11-10', '2011-11-12', '2011-11-11'),
 ('2011-11-10', '2011-11-11', '2011-11-12', '2011-11-13'),
 ('2011-11-10', '2011-11-20', '2011-11-11', '2011-11-19'),
 ('2011-11-11', '2011-11-19', '2011-11-10', '2011-11-20')) t(a,b,c,d);
 overlaps
----------
 t
 f
 f
 f
 f
 t
 t
(7 rows)

Issue Links

• links to: GitHub Pull Request #26702