This topic describes how to create scheduled tasks for a TTL table and how to view and delete the scheduled tasks of a TTL table.

After you create a scheduled task for a TTL table, the task is executed regularly to scan all local partitions of the TTL table. New local partitions are automatically created based on the scan results. Expired local partitions are also automatically deleted in the task. The expiration time of local partitions and the time when partitions are created in advance are specified by local_partition_definition specified in the CREATE TABLE statement that is executed to create the TTL table.

Create a scheduled task

CREATE SCHEDULE
    FOR partition_type
    ON [schemaName.]tableName
    CRON 'cron_expr'
    [TIMEZONE 'time_zone']

partition_type:
        LOCAL_PARTITION

cron_expr:
    '0 15 10 ? * *'   # The partition expires at 10:15 AM every day.
   '0 15 10 ? * 6L'  # The partition expires at 10:15 AM on the last Friday of each month.
   '0 11 11 11 11 ?' # The partition expires at 11:11 AM on November 11 every year.
   ...

time_zone:
    '+08:00'
   '+00:00'
   ...

View the scheduled tasks of a TTL table

When you create a TTL table, a default scheduled task is created to schedule the local partitions of the TTL table. If you specify the DISABLE SCHEDULE parameter when you create the TTL table, the default scheduled task is not created.

You can view the scheduled tasks of a TTL table in the INFORMATION_SCHEMA.LOCAL_PARTITIONS_SCHEDULE view.

Note The default scheduled task is executed only to scan the local partitions of the TTL table. The time when local partitions are created or deleted are determined by the parameters that you specify when you create the table. 
SELECT * FROM INFORMATION_SCHEMA.LOCAL_PARTITIONS_SCHEDULE\G;
*************************** 1. row ***************************
     SCHEDULE_ID: 21
     SCHEMA_NAME: local_partition
      TABLE_NAME: t_order
          STATUS: ENABLED
   SCHEDULE_EXPR: 0 0 12 1/5 * ?
SCHEDULE_COMMENT: at 12:00 every 5 days
       TIME_ZONE: +00:00
  LAST_FIRE_TIME: 1970-01-01 08:00:00
  NEXT_FIRE_TIME: 2021-11-01 12:00:00
1 row in set (0.03 sec)

View the execution results of scheduled tasks

show schedule result 21\G;
*************************** 1. row ***************************
SCHEDULE_ID: 21
  TIME_ZONE: +00:00
  FIRE_TIME: 2021-11-08 04:20
 START_TIME:
FINISH_TIME:
      STATE: QUEUED
     REMARK: NULL
 RESULT_MSG: NULL
*************************** 2. row ***************************
SCHEDULE_ID: 21
  TIME_ZONE: +00:00
  FIRE_TIME: 2021-11-08 04:19
 START_TIME: 2021-11-08 04:19:50
FINISH_TIME: 2021-11-08 04:19:50
      STATE: SUCCESS
     REMARK: NULL
 RESULT_MSG: NULL

Delete a scheduled task

DROP SCHEDULE schedule_id;

Examples

Example 1:

You can execute the following statement to create a scheduled task that specifies the local partition expires at 12:00 (UTC+00:00) every five days starting from the first day of each month. 

CREATE SCHEDULE
FOR LOCAL_PARTITION
ON `local_partition`.`t_order`
CRON '0 0 12 1/5 * ?'
TIMEZONE '+00:00';

Example 2

You can execute the following statement to create a scheduled task that specifies the local partition expires at 12:00 every five days starting from the first day of each month. 

CREATE SCHEDULE
FOR LOCAL_PARTITION
ON `local_partition`.`t_order`
CRON '0 0 12 1/5 * ?'
;

Example 3

You can execute the following statement to delete the scheduled task whose ID is 526. 

DROP SCHEDULE 526;