@Rocky how can i set the start date of a time_slice partition

In StarRocks, the way you set or define the start date for a partition depends on whether you are using Expression Partitioning (automatic) or Range Partitioning (manual/batch creation). 1. Range Partitioning (Setting a Start Date via

START

) If you want to create a series of partitions with a specific start date and a fixed interval (like 7 days), you use the

START

,

END

, and

EVERY

syntax. This is the most common way to "set" a start date for a series of partitions.

sql
CREATE TABLE site_access (
    event_day DATETIME NOT NULL,
    site_id INT DEFAULT '10',
    pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id)
PARTITION BY RANGE(event_day) (
    START ("2023-01-01") END ("2024-01-01") EVERY (INTERVAL 7 DAY)
)
DISTRIBUTED BY HASH(event_day, site_id);

* How it works: The first partition will start exactly at

2023-01-01

. * Granularity: You can use

INTERVAL 1 DAY

,

INTERVAL 1 WEEK

,

INTERVAL 1 MONTH

, etc. ────────── 2. Expression Partitioning (Automatic Alignment) If you use the

time_slice

function directly in the

PARTITION BY

clause, StarRocks uses Expression Partitioning. This method is automatic—partitions are created as data is loaded.

sql
PARTITION BY time_slice(event_day, INTERVAL 7 DAY)

* Start Date Alignment: The

time_slice

function always calculates intervals starting from A.D.

0001-01-01 00:00:00

. Since

0001-01-01

was a Monday,

INTERVAL 7 DAY

will naturally align all partitions to start on Mondays. * Setting a "Start Date": In this mode, you don't "set" a start date in the DDL. Instead: * Data Loading: The first partition is created based on the earliest date in your loaded data. * Manual Creation: If you need to pre-create a partition for a specific range, you can use `ALTER TABLE`:

sql
        ALTER TABLE site_access ADD PARTITION VALUES [('2023-01-02'), ('2023-01-09'));

* Retention: Use the property

"partition_live_number" = "N"

to control how many recent partitions are kept, which effectively moves the "start date" of your available data forward over time. ────────── 3. Adjusting Alignment (The "Trick") If you need

time_slice

to align to a specific day other than Monday (e.g., Sunday), you cannot pass a "start date" parameter to the function. However, you can offset the column within the expression (if supported in your version) or at the application level. Note: For Materialized Views, you can define a custom alignment by combining

date_trunc

and

time_slice

in the

SELECT

list:

sql
CREATE MATERIALIZED VIEW mv1
PARTITION BY date_trunc('day', mv_date)
AS 
SELECT 
    time_slice(original_date, INTERVAL 7 DAY) AS mv_date,
    sum(revenue)
FROM base_table
GROUP BY mv_date;

Summary * To manually specify the start of a partition series: Use

PARTITION BY RANGE (...) (START ("YYYY-MM-DD") ... EVERY (...))

. * To use automatic partitioning with `time_slice`: The start is fixed to

0001-01-01

. You manage which partitions exist by loading data or using

partition_live_number

. References • sql-reference/sql-functions/date-time-functions/time_slice.md • sql-reference/sql-functions/date-time-functions/date_slice.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • administration/management/timezone.md