Partition data by year and day using the YYYYDD function - PolarDB:YYYYDD

YYYYDD is a date-based sharding function for PolarDB-X 1.0 that routes rows to database shards based on the year and day-of-year extracted from a date or time shard key.

How it works

YYYYDD computes a shard index from a date or time value using the following formula:

(YYYY × 366 + DD) % D

Variable	Description
`YYYY`	The year portion of the shard key value
`DD`	The day of the year (1–366)
`D`	The total number of database shards

The remainder determines which database shard stores the row.

Example: For 2012-12-31 12:12:12, December 31 is the 366th day of 2012. The shard index is (2012 × 366 + 366) % D.

Limitations

Constraint	Detail
Shard key type	Must be `DATE`, `DATETIME`, or `TIMESTAMP`
Shard count	Must be determined before using the function, based on a specific cycle (such as two years)
Cycle rollover	When a date recurs in the next cycle, rows may land in the same shard as rows from the same date in the previous cycle. For example, in a two-year cycle starting March 1, 2012, rows for March 1, 2014 may route to the same shard as rows for March 1, 2012. The exact shard depends on the total table shard count.
Minimum version	PolarDB-X 1.0 instance version 5.1.28-1320920 or later

Use cases

YYYYDD suits workloads that partition data by year and day of year — for example, time-series logs or event records filtered by a specific date. Use it with tbpartition by YYYYDD(ShardKey) so that rows from the same day are co-located in a single physical table shard, enabling queries with a shard key predicate to target one shard without scanning others.

Create a sharded table

The following example creates a table partitioned by create_time, covering a two-year cycle.

Calculate the number of table shards

Determine the total number of physical table shards before writing the Data Definition Language (DDL) statement:

Choose a cycle length. A year has up to 366 days, so a two-year cycle requires at most 732 table shards (366 × 2).
Divide the total by the number of database shards and round up to the nearest integer.

In this example, a PolarDB-X 1.0 instance has two nodes with eight database shards each, giving 16 database shards in total:

732 ÷ 16 = 45.75  →  round up to 46

Set the table shard count to 46.

For best performance, choose a table shard count that is an integer multiple of the number of database shards.

DDL statement

CREATE TABLE test_yyyydd_tb (
    id          INT,
    name        VARCHAR(30) DEFAULT NULL,
    create_time DATETIME    DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
  dbpartition  BY YYYYDD(create_time)
  tbpartition  BY YYYYDD(create_time) TBPARTITIONS 46;

Queries that include create_time in the WHERE clause are routed directly to the matching physical table shard without scanning other shards.