FlinkSQL Function Enhancements

This section applies to MRS 3.5.0 or later.

DATE_ADD Function

The DATE_ADD function is used to return the date after a specified number of days are added to a date.

Parameters
- 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.
- Return date: The date obtained by adding the specified number of days to the date. The data type is STRING.

Example SQL

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** Return value
date1 (string)	date2 (string)	date3 (string)
2024-06-28	2017-10-15	null

DATE_SUB Function

The DATE_SUB function is used to return the date obtained by subtracting a specified number of days from a date.

Parameter
- 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.
- Return date: The date obtained by subtracting the specified number of days from the date. The data type is STRING.

Example SQL

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