Help Center/ TaurusDB/ Kernel/ Partitioning Enhancements/ INTERVAL RANGE

Updated on 2026-08-04 GMT+08:00

INTERVAL RANGE

Scenarios

An INTERVAL RANGE partitioned table is an extension of a RANGE partitioned table. If data to be inserted into a RANGE partitioned table falls outside the range of an existing partition, it cannot be inserted and an error will be returned.

If there is an INTERVAL RANGE partitioned table in a database, the database can create a partition based on rules specified by the INTERVAL clause when data to be inserted exceeds the range of an existing partition.

Prerequisites

The kernel version of your TaurusDB instance must be 2.0.54.240600 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
rds_interval_range_enabled has been set to ON.

Constraints

INTERVAL RANGE partitioned tables support only HASH or KEY subpartitions.
If an INTERVAL RANGE rule is in RANGE COLUMNS(column_list) INTERVAL([type], value) format:
- column_list specifies only a single partition key, which must be of the INTEGER, DATE, TIME, or DATETIME type.
- If the partition key is of the INTEGER type, the interval type (type) can be left blank.
- If the partition key is of the DATE type, the interval type (type) can only be YEAR, QUARTER, MONTH, WEEK, or DAY.
- If the partition key is of the TIME type, the interval type (type) can only be HOUR, MINUTE, or SECOND.
- If the partition key is of the DATETIME type, the interval type (type) can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND.
- The interval value (value) must be a positive integer.
- If the interval type (type) is SECOND, the interval cannot be less than 60.
If an INTERVAL RANGE rule is in RANGE(expr) INTERVAL(value) format, expr must be an integer, and value must be a positive integer.
You cannot execute the INSERT ... SELECT, INSERT ... ON DUPLICATE KEY UPDATE, and UPDATE statements to add partitions.
When you execute the LOAD DATA statement to import data, automatic partition creation will not be triggered. (If the range of the partition covers all data, data can be imported. If the range of the partition does not cover all data, automatic partition creation will not be triggered and data cannot be imported.)
Partitions cannot be added in explicit transactions or in SQL statements following set autocommit=0.
Prefix _p is reserved for automatically created partitions. If you use this prefix for custom partitions, automatic partition creation may fail.
The SET INTERVAL([type], value) clause applies only to INTERVAL RANGE and RANGE partitioned tables. If these tables have subpartitions, the subpartitions must be of the HASH or KEY type.
The values of type and value in the SET INTERVAL([type], value) clause must be restricted by the partition expression expr or the partition key column_list of the original table.

How to Use

**Table 1** Parameter description
Parameter	Level	Description
rds_interval_range_enabled	Global	Enables or disables INTERVAL RANGE. Value: ON: INTERVAL RANGE is enabled. OFF: INTERVAL RANGE is disabled.

Creating an INTERVAL RANGE Partitioned Table

The definition format of an INTERVAL RANGE partitioned table is similar to that of a RANGE partitioned table. The only difference is that the INTERVAL clause is added.

Syntax

CREATE TABLE [IF NOT EXISTS] [schema.]table_name 
table_definition
partition_options;

partition_options is:

PARTITION BY
    RANGE {(expr) | COLUMNS(column_list)}
    {INTERVAL(value) | INTERVAL(type ,value)}
    (partition_definition [, partition_definition] ...)

partition_definition is:

PARTITION partition_name
    [VALUES LESS THAN {expr | MAXVALUE}]
    [[STORAGE] ENGINE [=] engine_name]
    [COMMENT [=] 'string' ]
    [DATA DIRECTORY [=] 'data_dir']
    [INDEX DIRECTORY [=] 'index_dir']
    [MAX_ROWS [=] max_number_of_rows]
    [MIN_ROWS [=] min_number_of_rows]
    [TABLESPACE [=] tablespace_name]

The INTERVAL clause supports only the interval type (type) and interval value (value).

**Table 2** Parameter description
Parameter	Description
expr	The expression of the partition. It is used in RANGE(expr) and must be of the integer type.
column_list	A partitioning column list. It is used in RANGE COLUMNS(column_list). In an INTERVAL RANGE partitioned table, column_list can only be a single column.
INTERVAL(value)	The format of the INTERVAL clause when RANGE COLUMNS(column_list) with an integer column or RANGE(expr) is used. value indicates the interval value, which must be a positive integer.
INTERVAL(type, value)	The format of the INTERVAL clause when RANGE COLUMNS(column_list) is used and column_list is of the DATE, TIME, or DATETIME type. type indicates the interval type and its value can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. value indicates the interval value, which must be a positive integer. When type is set to SECOND, the interval value cannot be less than 60.

**Table 3** INTERVAL clause parameters
Parameter	Description	Example Value
Interval value (value)	Add N consecutive numbers to a partition.	INTERVAL(1000)
Interval type (YEAR)	Set the interval type to YEAR and add the data of one year to a partition.	INTERVAL(YEAR, 1)
Interval type (QUARTER)	Set the interval type to QUARTER and add the data of one quarter to a partition.	INTERVAL(QUARTER, 1)
Interval type (MONTH)	Set the interval type to MONTH and add the data of one month to a partition.	INTERVAL(MONTH, 1)
Interval type (WEEK)	Set the interval type to WEEK and add the data of one week to a partition.	INTERVAL(WEEK, 1)
Interval type (DAY)	Set the interval type to DAY and add the data of one day to a partition.	INTERVAL(DAY, 1)
Interval type (HOUR)	Set the interval type to HOUR and add the data of one hour to a partition.	INTERVAL(HOUR, 1)
Interval type (MINUTE)	Set the interval type to MINUTE and add the data of one minute to a partition.	INTERVAL(MINUTE, 1)
Interval type (SECOND)	Set the interval type to SECOND and add the data of 60 seconds to a partition.	INTERVAL(SECOND, 60)

Examples

The following example uses order_time as the partition key to partition the sales table by interval.

Create an INTERVAL RANGE partitioned table in a database and insert data into a table. Example:

CREATE TABLE sales
(
  id BIGINT,
  uid BIGINT,
  order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
  PARTITION p0 VALUES LESS THAN('2021-9-1')
);

Insert data into the INTERVAL RANGE partitioned table. Example:
```
INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');
```

After data is inserted, execute the SHOW CREATE TABLE statement to query the sales table definition. The new table definition is as follows:

CREATE TABLE `sales` (
  `id` bigint DEFAULT NULL,
  `uid` bigint DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800220201 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
 PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */

In the preceding example, three partitions _p20211001000000, _p20211101000000, and _p20211201000000 are automatically added to the INTERVAL RANGE partition. Partition names prefixed with _p are reserved by the system. Such partition names are not recommended when you create or rename partitions.

INTERVAL RANGE partitioned tables support HASH or KEY subpartitions. Example:

CREATE TABLE sales_ir_key
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(month(date)) INTERVAL(1)
SUBPARTITION BY KEY(date) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN(4)
    (SUBPARTITION sp_001,
     SUBPARTITION sp_002),
  PARTITION q2_2012 VALUES LESS THAN(7)
    (SUBPARTITION sp_003,
     SUBPARTITION sp_004)
);
CREATE TABLE sales_ir_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(date) INTERVAL(YEAR, 1)
SUBPARTITION BY HASH(TO_DAYS(date)) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN('2021-01-01')
    (SUBPARTITION sp_001,
     SUBPARTITION sp_002),
  PARTITION q2_2012 VALUES LESS THAN('2022-01-01')
    (SUBPARTITION sp_003,
     SUBPARTITION sp_004)
);

Conversion Between INTERVAL RANGE Partitioned Tables and Other Types of Tables

Other types of tables can be converted to INTERVAL RANGE partitioned tables, and vice versa.

Syntax

Convert other types of tables to INTERVAL RANGE partitioned tables.

ALTER TABLE table_name 
table_definition 
partition_options;

partition_options is:

PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, value) | INTERVAL(value) }
    [(partition_definition [, partition_definition] ...)]

partition_definition is:

PARTITION partition_name
     [VALUES LESS THAN {expr | MAXVALUE}]
     [[STORAGE] ENGINE [=] engine_name]
     [COMMENT [=] 'string' ]
     [DATA DIRECTORY [=] 'data_dir']
     [INDEX DIRECTORY [=] 'index_dir']
     [MAX_ROWS [=] max_number_of_rows]
     [MIN_ROWS [=] min_number_of_rows]
     [TABLESPACE [=] tablespace_name]

The parameters associated with the INTERVAL clause are as follows.

**Table 4** Parameter description
Parameter	Description
expr	The expression of the partition. It is used in RANGE(expr) and must be of the integer type.
column_list	A partitioning column list. It is used in RANGE COLUMNS(column_list). In an INTERVAL RANGE partitioned table, column_list can only be a single column.
INTERVAL(value)	The format of the INTERVAL clause when RANGE COLUMNS(column_list) with an integer column or RANGE(expr) is used. value indicates the interval value, which must be a positive integer.
INTERVAL(type, value)	The format of the INTERVAL clause when RANGE COLUMNS(column_list) is used and column_list is of the DATE, TIME, or DATETIME type. type indicates the interval type and its value can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. value indicates the interval value, which must be a positive integer. When type is set to SECOND, the interval value cannot be less than 60.

Convert an INTERVAL RANGE partitioned table to another type of table. partition_options is optional.
```
ALTER TABLE table_name table_definition
[partition_options];
```

Examples

Convert other types of tables to INTERVAL RANGE partitioned tables.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
);
ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

Convert an INTERVAL RANGE partitioned table to another type of table.

CREATE TABLE orders (a INT, b DATETIME)
PARTITION BY RANGE (a) INTERVAL(10)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
ALTER TABLE orders PARTITION BY LIST COLUMNS (a)
(
    PARTITION p0 VALUES IN (1, 11, 25)
);

Modify the INTERVAL clause in the INTERVAL RANGE partitioned table.

CREATE TABLE orders (a INT, b DATETIME)
PARTITION BY RANGE (a) INTERVAL(10)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
ALTER TABLE orders PARTITION BY RANGE (a) INTERVAL(20)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
# Delete the INTERVAL clause.
ALTER TABLE orders PARTITION BY RANGE (a)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
# Add the INTERVAL clause.
ALTER TABLE orders PARTITION BY RANGE (a) INTERVAL(100)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);

SET INTERVAL Clause Usage

You can use the SET INTERVAL clause to modify the interval type and value of the INTERVAL clause defined in the INTERVAL RANGE partitioned table, or eliminate or add the INTERVAL clause.

Syntax

ALTER TABLE table_name SET INTERVAL {() | (type, value) | (value)};

**Table 5** Parameter description
Parameter	Description
type	The type of the interval. Its value can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. If you do not specify this parameter, the numeric type is used by default.
value	The value of the interval. When type is set to SECOND, the interval value cannot be less than 60.

Examples

Modify the interval type and value in the INTERVAL RANGE partitioned table.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
ALTER TABLE orders SET INTERVAL(YEAR, 1);

Convert a RANGE partitioned table to an INTERVAL RANGE partitioned table.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
# Delete the INTERVAL clause.
ALTER TABLE sales SET INTERVAL();
# Add the INTERVAL clause.
ALTER TABLE sales SET INTERVAL(DAY, 60);

The ALTER TABLE table_name SET INTERVAL() statement can be used even if rds_interval_range_enabled is disabled. This statement is used to eliminate the definition of the INTERVAL clause in an INTERVAL RANGE partitioned table and convert the partitioned table to a RANGE partitioned table.

Parent Topic: Partitioning Enhancements

Previous topic: LIST DEFAULT HASH

Next topic: Partition-level MDL

INTERVAL RANGE

Scenarios

Prerequisites

Constraints

How to Use

Creating an INTERVAL RANGE Partitioned Table

Syntax

Examples

Conversion Between INTERVAL RANGE Partitioned Tables and Other Types of Tables

Syntax

Examples

SET INTERVAL Clause Usage

Syntax

Examples

What is your overall rating for this page?