Updated on 2025-08-22 GMT+08:00

Flink SQL Function Enhancements

This section applies only to MRS 3.5.0 or later.

DATE_ADD Function

The DATE_ADD function returns a new date by adding a specified number of days to a given date.

  • Description
    • Given date: The data type is TIMESTAMP or STRING (format: yyyy-MM-dd HH:mm:ss). The value can be NULL.
    • Number of days to add: The data type is INT.
    • New date: The date obtained by adding the specified number of days to the given date. The data type is STRING.
  • Example SQL statements:
    CREATE TABLE source (
      time1 TIMESTAMP
    ) WITH (
      'connector' = 'datagen',
      'rows-per-second' = '1'
    );
    create table Sink (
      date1 string,
      date2 string,
      date3 string
    ) with ('connector' = 'print');
    INSERT into
      Sink
    select
      DATE_ADD(time1, 30) as date1,
      DATE_ADD('2017-09-15 00:00:00', 30) as date2,
      DATE_ADD(cast(null as timestamp),30) as date3
    FROM source
    Table 1 Returned result

    date1 (string)

    date2 (string)

    date3 (string)

    2024-06-28

    2017-10-15

    null

DATE_SUB Function

The DATE_SUB function returns a new date by subtracting a specified number of days from a given date.

  • Description
    • Given date: The data type is TIMESTAMP or STRING (format: yyyy-MM-dd HH:mm:ss). The value can be NULL.
    • Number of days to subtract: The data type is INT.
    • New date: The date obtained by subtracting the specified number of days from the given date. The data type is STRING.
  • Example SQL statements:
    CREATE TABLE source (
      time1 TIMESTAMP
    ) WITH (
      'connector' = 'datagen',
      'rows-per-second' = '1'
    );
    create table Sink (
      date1 string,
      date2 string,
      date3 string
    ) with ('connector' = 'print');
    INSERT into
      Sink
    select
      DATE_SUB(time1,30) as date1,
      DATE_SUB('2017-09-15 00:00:00', 30) as date2,
      DATE_SUB(cast(null as timestamp),30) as date3
    FROM source
    Table 2 Returned result

    date1 (string)

    date2 (string)

    date3 (string)

    2024-04-29

    2017-08-16

    null