Shard databases and tables by year and week with the YYYYWEEK function - PolarDB

YYYYWEEK partitions data into database shards and table shards based on the year and ISO week number of a DATE, DATETIME, or TIMESTAMP column.

How it works

YYYYWEEK extracts the year and ISO week number from the shard key value and computes (year × 54 + ISO_week_number) % D, where D is the number of database shards. The result determines which shard receives the row.

Week that spans a year boundary: ISO week numbering can assign the last few days of December to the first week of the following year. For example, December 31, 2012 falls in ISO week 1 of 2013, so YYYYWEEK('2012-12-31 12:12:12') evaluates to (2013 × 54 + 1) % D, not (2012 × 54 + 52) % D.

Data reuse after a cycle ends: When a week recurs after the defined cycle completes, data may be routed to the same shard as earlier data from that same week. For example, with a two-year cycle starting in week 1 of 2012, data from week 1 of 2014 may land in the same shard as data from week 1 of 2012. The exact shard depends on the total number of table shards.

When to use YYYYWEEK

Use YYYYWEEK when:

Your queries filter or aggregate data by year and week — for example, weekly sales reports or weekly active user counts.
Data spans multiple years and cross-year week alignment matters. WEEK resets the week number each year, while YYYYWEEK preserves year context.
You need weekly granularity over a multi-year range. Use YYYYMM for monthly granularity or YYYYWEEK for weekly granularity.

Pair YYYYWEEK with tbpartition by YYYYWEEK(ShardKey) to route both database shards and table shards by the same key. This enables direct shard lookup for queries that include the partitioning key.

Limits

The partitioning key must be of type DATE, DATETIME, or TIMESTAMP.
YYYYWEEK creates one table shard per week within the defined cycle. Calculate the total number of table shards before you create the table.
The PolarDB-X 1.0 instance version must be 5.1.28-1320920 or later.

Create a table with YYYYWEEK

Step 1: Calculate the required number of table shards

Decide on a cycle length, then calculate:

Total table shards = weeks per year × cycle length in years. A year has up to 53 ISO weeks, so a two-year cycle requires 53 × 2 = 106 table shards.
Table shards per database shard = total table shards ÷ number of database shards, rounded up to the nearest integer. Keep this value an integer multiple of the number of database shards when possible.

Example: With 8 database shards and a two-year cycle:

Total table shards: 53 × 2 = 106
Table shards per database shard: 106 ÷ 8 = 13.25, rounded up to 14

Step 2: Create the table

Use a Data Definition Language (DDL) statement with dbpartition by YYYYWEEK and tbpartition by YYYYWEEK. Set tbpartitions to the value calculated in Step 1.

The following example creates a table with 8 database shards and 14 table shards per database shard, covering a two-year cycle:

create table test_yyyyweek_tb (
    id int,
    name varchar(30) DEFAULT NULL,
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by YYYYWEEK(create_time)
tbpartition by YYYYWEEK(create_time) tbpartitions 14;