数据湖探索 DLI-时间函数:OVERLAPS

时间：2024-08-15 10:29:30

数据湖探索 DLI 内置函数

OVERLAPS

功能描述
若两个时间范围有重叠，则返回TRUE，反之，则返回FALSE。

语法说明

BOOLEAN (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)

入参说明

参数名	数据类型	参数说明
timepoint1/timepoint2	DATE/TIME/TIMESTAMP	时间点。
temporal1/temporal2	DATE/TIME/TIMESTAMP/INTERVAL	时间点或时间间隔。

(timepoint, temporal)在判断是否重叠时为闭区间。
temporal可以是DATE/TIME/TIMESTAMP也可以是INTERVAL。
- 当temporal是DATE/TIME/TIMESTAMP时，(timepoint, temporal)表示timepoint, temporal之间的时间间隔。允许temporal在timepoint之前，如(DATE '1997-04-25', DATE '1997-04-23')也合法。
- 当temporal是INTERVAL时，(timepoint, temporal)表示timepoint, timepoint+temporal之间的时间间隔。
必须保证(timepoint1, temporal1)和(timepoint2, temporal2)是同一数据类型的时间间隔。

示例

测试语句

SELECT 
	(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) AS `result`,
        (TIME '2:30:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) AS `result2`,
	(TIME '2:30:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:31:00', INTERVAL '2' HOUR) AS `result3`,
	(TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:00:00', INTERVAL '3' HOUR) AS `result4`,
	(TIMESTAMP '1997-04-25 12:00:00', TIMESTAMP '1997-04-25 12:20:00') OVERLAPS (TIMESTAMP '1997-04-25 13:00:00', INTERVAL '2' HOUR) AS `result5`,
	(DATE '1997-04-23', INTERVAL '2' DAY) OVERLAPS (DATE '1997-04-25', INTERVAL '2' DAY) AS `result6`,
	(DATE '1997-04-25', DATE '1997-04-23') OVERLAPS (DATE '1997-04-25', INTERVAL '2' DAY) AS `result7`
FROM
	testtable;

测试结果