Snowflake: create OVERLAPS function

Create OVERLAPS (t1,t2,t3,t4) function at Snowflake database

Related links:

• Postgres SQL OVERLAPS
• Intersection of 1D segments
• Snowflake: split string into array, sort it, and return string.

Creating OVERLAPS  UDF function at Snowflake database:

CREATE OR REPLACE FUNCTION  overlaps(V_A_START FLOAT           , V_A_END FLOAT           , V_B_START FLOAT           , V_B_END FLOAT           )
 --create or replace FUNCTION overlaps(V_A_START FLOAT4          , V_A_END FLOAT4          , V_B_START FLOAT4          , V_B_END FLOAT4          )
 --create or replace FUNCTION overlaps(V_A_START FLOAT8          , V_A_END FLOAT8          , V_B_START FLOAT8          , V_B_END FLOAT8          )
 --create or replace FUNCTION overlaps(V_A_START DOUBLE          , V_A_END DOUBLE          , V_B_START DOUBLE          , V_B_END DOUBLE          )
 --create or replace FUNCTION overlaps(V_A_START DOUBLE PRECISION, V_A_END DOUBLE PRECISION, V_B_START DOUBLE PRECISION, V_B_END DOUBLE PRECISION)
 --create or replace FUNCTION overlaps(V_A_START REAL            , V_A_END REAL            , V_B_START REAL            , V_B_END REAL            )
 --create or replace FUNCTION overlaps(V_A_START DATE            , V_A_END DATE            , V_B_START DATE            , V_B_END DATE            )
 --create or replace FUNCTION overlaps(V_A_START DATETIME        , V_A_END DATETIME        , V_B_START DATETIME        , V_B_END DATETIME        )
 --create or replace FUNCTION overlaps(V_A_START TIMESTAMP       , V_A_END TIMESTAMP       , V_B_START TIMESTAMP       , V_B_END TIMESTAMP       )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_LTZ   , V_A_END TIMESTAMP_LTZ   , V_B_START TIMESTAMP_LTZ   , V_B_END TIMESTAMP_LTZ   )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_NTZ   , V_A_END TIMESTAMP_NTZ   , V_B_START TIMESTAMP_NTZ   , V_B_END TIMESTAMP_NTZ   )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_TZ    , V_A_END TIMESTAMP_TZ    , V_B_START TIMESTAMP_TZ    , V_B_END TIMESTAMP_TZ    )
RETURNS BOOLEAN NOT NULL
LANGUAGE javascript
AS     
$$ 
    RETURN (Math.max(V_A_START,V_A_END) >= Math.min(V_B_START,V_B_END) && Math.max(V_B_START,V_B_END) >= Math.min(V_A_START,V_A_END)) ; 
$$
;

These overloads will NOT work and fail rendering the following error: 

                 Language JAVASCRIPT does not support type 'NUMBER(38,0)' for argument or return type.

-- create or replace FUNCTION overlaps(V_A_START NUMBER          , V_A_END NUMBER          , V_B_START NUMBER          , V_B_END NUMBER          )   
-- create or replace FUNCTION overlaps(V_A_START DECIMAL         , V_A_END DECIMAL         , V_B_START DECIMAL         , V_B_END DECIMAL         )
-- create or replace FUNCTION overlaps(V_A_START NUMERIC         , V_A_END NUMERIC         , V_B_START NUMERIC         , V_B_END NUMERIC         )
-- create or replace FUNCTION overlaps(V_A_START INT             , V_A_END INT             , V_B_START INT             , V_B_END INT             )
-- create or replace FUNCTION overlaps(V_A_START INTEGER         , V_A_END INTEGER         , V_B_START INTEGER         , V_B_END INTEGER         )
-- create or replace FUNCTION overlaps(V_A_START BIGINT          , V_A_END BIGINT          , V_B_START BIGINT          , V_B_END BIGINT          )
-- create or replace FUNCTION overlaps(V_A_START SMALLINT        , V_A_END SMALLINT        , V_B_START SMALLINT        , V_B_END SMALLINT        )
-- create or replace FUNCTION overlaps(V_A_START TINYINT         , V_A_END TINYINT         , V_B_START TINYINT         , V_B_END TINYINT         )
-- create or replace FUNCTION overlaps(V_A_START BYTEINT         , V_A_END BYTEINT         , V_B_START BYTEINT         , V_B_END BYTEINT         )

Verify the working logic:

SELECT overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-02 00:00:00') AS OVERLAPS_LEFT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS OVERLAPS_RIGHT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:01', TIMESTAMP'2019-01-02 23:59:59') AS OVERLAPS_INISDE
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS OVERLAPS_OUTSIDE
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-01 23:59:59') AS NOT_OVERLAPS_LEFT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-03 00:00:01', TIMESTAMP'2019-01-04 00:00:00') AS NOT_OVERLAPS_RIGHT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',NULL, NULL) AS NOT_OVERLAPS_NULLS
      ,overlaps( NULL, TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS NOT_OVERLAPS_NULLS
      ,overlaps(TIMESTAMP'2019-01-03 00:00:00', TIMESTAMP'2019-01-02 00:00:00' ,TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-02 00:00:00') AS OVERLAPS_reverseda
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00',TIMESTAMP'2019-01-01 00:00:00'  ) AS OVERLAPS_reversedb
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00',TIMESTAMP'2019-01-01 00:00:00'  ) AS OVERLAPS_reversedboth
;

Finally, check the all existent overloads of the function:

show functions like 'overlaps';