System Versioning allows storing the historical data in separate partitions partitioned by a time interval. (see https://mariadb.com/kb/en/library/system-versioned-tables/#storing-the-history-separately). This would be a helpful functionality if it were possible to set the start datatime for the interval, e.g. the start of the fiscal year. This way the data could be partitioned by fiscal year which would make it easy to drop ald partitions for which the retention time as expired. Without this interval adjustment removing old data would result in a DELETE ... WHERE ...

Proposal for syntax

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING

  PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS '2018-10-01 00:00:00'  (

    PARTITION p0 HISTORY,

    PARTITION p1 HISTORY,

    PARTITION p2 HISTORY,

    PARTITION pcur CURRENT

  );

Default STARTS rounding depending on INTERVAL type

If STARTS clause is omitted, a default one is assigned with value
derived from query timestamp. The rounding is done on STARTS value
depending on INTERVAL type:

SECOND: no rounding is done;
MINUTE: timestamp seconds is set to 0;
HOUR: timestamp seconds and minutes are set to 0;
DAY, WEEK, MONTH and YEAR: timestamp seconds, minutes and hours are
set to 0 (the date of rotation is kept as current date).