YYYYDD

This topic describes how to use the YYYYDD function.

Description

You can use the YYYYDD function to calculate hash values based on the years and days in the time values of a database shard key. Then, the function divides the hash values by the number of database shards to obtain the remainders. As a result, data is partitioned based on the remainders.

For example, the YYYYDD('2012-12-31 12:12:12') function is equivalent to 2012x366+366)%D. D specifies the number of database shards into which data is partitioned. The calculation result indicates that December 31, 2012 is the 366th day of 2012.

Limits

• A shard key must be of the DATE, DATETIME, or TIMESTAMP type.

• Before you use the YYYYDD function, determine the total number of physical table shards that are required. You can determine this number based on a cycle, such as two years. The YYYYDD function can be used only to create an independent table shard for each day within a cycle.

• When a date reoccurs after a cycle ends, data generated on the date may be partitioned into the same table shard in the same database shard. For example, after a two-year cycle, March 1, 2014 corresponds to March 1, 2012. The data generated on March 1, 2014 may be partitioned into the same table shard as the data generated on March 1, 2012. The table shard to which the data is partitioned is determined based on the number of table shards.

Scenarios

The YYYYDD function is suitable for scenarios in which data needs to be partitioned into database shards by year and day of the year. We recommend that you use this function and the tbpartition by YYYYDD(ShardKey) clause together.

Examples

A Distributed Relational Database Service (DRDS) instance contains eight physical database shards. The following requirements must be met:PolarDB-X

• Data is partitioned into the database shards by year and day of the year.

• Data generated on the same day is partitioned into the same table shard. Each day within two years corresponds to an independent table shard.

• A query is directly distributed to a specific physical table shard of a database shard if shard keys are specified in the query.

If each day within two years needs to correspond to a table shard, a total of 732 physical table shards must be created. This is because a year has up to 366 days. The total number of physical table shards that must be created is calculated by using the following equation: 366 × 2 = 732. Ninety-two physical table shards must be created in each database shard because a PolarDB-X instance has eight database shards. To obtain the number of physical table shards in each database shard, divide the total number of physical table shards that must be created by the number of database shards. Then, round the result up to the next nearest integer. In this case, the result is 92. This result is calculated by using the following equation: 732/8 = 91.5. We recommend that the number of table shards be an integer multiple of the number of database shards.

To meet these requirements, execute the following 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 92;