The topic describes how to use the DATE_SUB function. This function subtracts specific days from a date and then returns the obtained date.

Limits

This function is supported only in Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.0 or later.

Syntax

DATE DATE_SUB(VARCHAR startdate, INT days)
DATE DATE_SUB(TIMESTAMP time, INT days)

Input parameters

Parameter Data type
startdate VARCHAR
Note A date of the VARCHAR type is in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format.
time TIMESTAMP
days INT
Note If an input parameter is NULL or a parsing error occurs, NULL is returned.

Example

  • Test data
    date1(VARCHAR) nullstr(VARCHAR)
    2017-10-15 NULL
  • Test statement
    SELECT DATE_SUB(date1, 30) as var1,
     DATE_SUB(TIMESTAMP '2017-10-15 23:00:00',30) as var2,
     DATE_SUB(nullstr,30) as var3
    FROM T1;
  • Test result
    var1(DATE) var2(DATE) var3(DATE)
    2017-09-15 2017-09-15 NULL