Calculate date difference by using the DATEDIFF function - Realtime Compute for Apache Flink

Returns the number of days between two dates as enddate - startdate.

Limits

Supported only in Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.0 or later.

Syntax

INT DATEDIFF(VARCHAR enddate, VARCHAR startdate)
INT DATEDIFF(TIMESTAMP enddate, VARCHAR startdate)
INT DATEDIFF(VARCHAR enddate, TIMESTAMP startdate)
INT DATEDIFF(TIMESTAMP enddate, TIMESTAMP startdate)

Parameters

Parameter	Data type
enddate	TIMESTAMP or VARCHAR
startdate	TIMESTAMP or VARCHAR

The result equals enddate - startdate. When enddate is earlier than startdate, the result is negative.

VARCHAR date formats: yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.

Return value

An INT representing the number of days from startdate to enddate. Returns NULL if either input is NULL or a parsing error occurs.

Example

SELECT
  DATEDIFF(datetime1, datetime2)                                            AS int1,  -- 30
  DATEDIFF(TIMESTAMP '2017-10-15 23:00:00', datetime2)                     AS int2,  -- 30
  DATEDIFF(datetime2, TIMESTAMP '2017-10-15 23:00:00')                     AS int3,  -- -30
  DATEDIFF(datetime2, nullstr)                                              AS int4,  -- NULL
  DATEDIFF(nullstr, TIMESTAMP '2017-10-15 23:00:00')                       AS int5,  -- NULL
  DATEDIFF(nullstr, datetime2)                                              AS int6,  -- NULL
  DATEDIFF(TIMESTAMP '2017-10-15 23:00:00', TIMESTAMP '2017-9-15 00:00:00') AS int7  -- 30
FROM T1;

Test data:

datetime1 (VARCHAR)	datetime2 (VARCHAR)	nullstr (VARCHAR)
2017-10-15 00:00:00	2017-09-15 00:00:00	NULL