# `VERSION::6.5.0` `RANGE INTERVAL` Partition - Example

```
CREATE TABLE subscribers (
 sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE NOT NULL
 )
 ~!@PARTITION BY RANGE COLUMNS (joined)@!~
 ~!@INTERVAL (1 YEAR) FIRST PARTITION LESS THAN ('2021-01-01') LAST PARTITION LESS THAN ('2026-01-01')@!~;

tidb> SELECT TABLE_NAME, GROUP_CONCAT(partition_name ORDER BY partition_name) 
   -> FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+--------------------+------------------------------------------------------------------------------------------------+
|     TABLE_NAME     | GROUP_CONCAT(partition_name ORDER BY partition_name)                                           |
+--------------------+------------------------------------------------------------------------------------------------+
|    subscribers     | P_LT_2021-01-01,P_LT_2022-01-01,P_LT_2023-01-01,P_LT_2024-01-01,P_LT_2025-01-01,P_LT_2026-01-01|
+--------------------+------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec) 
```

------------------------------------------------------------------------------
When we use `RANGE` partitioning or `RANGE COLUMNS` partitioning, sometimes we may encounter the need to create a large number of partitions in advance, which makes the SQL statements lengthy. In such case, we can use the `RANGE INTERVAL` partitioning, which is an extension of `RANGE` partitioning and can help us automatically create new partitions for the table. For example:

```
CREATE TABLE subscribers (
 sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS (joined)
INTERVAL (1 YEAR) FIRST PARTITION LESS THAN ('2021-01-01') LAST PARTITION LESS THAN ('2026-01-01');

SELECT TABLE_NAME, GROUP_CONCAT(partition_name ORDER BY partition_name) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+-------------------------------------------------------------------------------------------------+
| TABLE_NAME  | GROUP_CONCAT(partition_name ORDER BY partition_name)                                            |
+-------------+-------------------------------------------------------------------------------------------------+
| subscribers | P_LT_2021-01-01,P_LT_2022-01-01,P_LT_2023-01-01,P_LT_2024-01-01,P_LT_2025-01-01,P_LT_2026-01-01 |
+-------------+-------------------------------------------------------------------------------------------------+
```

You created a `RANGE INTERVAL` partitioned table, `INTERVAL (1 YEAR) FIRST PARTITION LESS THAN ('2021-01-01') LAST PARTITION LESS THAN ('2026-01-01')` means that it automatically create partitions with a one-year interval from `2021-01-01` to `2026-01-01`, and the prefix of the partition names are generated as `P_LT_`, followed by the maximum boundary value of the partition. The optional parameter `MAXVALUE PARTITION` will create a last partition with the value `PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)`. The optional parameter `NULL PARTITION` creates a partition where the value deduced from the partition expression is `NULL`. In the partitioned table context, `NULL` is considered smallest value.

`INTERVAL` partitioning also provides simpler and more user-friendly syntax for adding and removing partitions. The following statement will modify the first partition, it will delete all partitions that are less than the given expression and make the matching partition become the first partition after the operation. It does not affect the `NULL PARTITION`.

```
ALTER TABLE table_name FIRST PARTITION LESS THAN (<expression>)
```

The following statement will modify the last partition, it adds a new partition and expand the partition range up to the value of the given expression. If there is a `MAXVALUE PARTITION`, this statement will not take effect as it requires data reorganization.

```
ALTER TABLE table_name LAST PARTITION LESS THAN (<expression>)
```

More details: `https://docs.pingcap.com/tidb/stable/partitioned-table#range-interval-partitioning`.