Application-Time Periods, Bitemporal Modeling Concepts, and Constraint Design

Learning outcomes

ServiceHub signs customer support contracts with prices that are valid for business-defined date ranges. A correction entered today may say a price was actually valid last month. System time alone cannot model that distinction. MariaDB application-time periods represent when the application says a fact is valid; combining them with system-versioning creates a bitemporal model.

1. Two clocks, two different questions

If a contract effective on July 1 is entered on July 15, system time starts July 15 while application time can start July 1. Losing that distinction makes retroactive corrections impossible to reason about.

2. Create a contract-price period

DROP DATABASE IF EXISTS advanced21_l2;CREATE DATABASE advanced21_l2;USE advanced21_l2;CREATE TABLE contract_price (  contract_id BIGINT NOT NULL,  valid_from DATE NOT NULL,  valid_to DATE NOT NULL,  monthly_price DECIMAL(10,2) NOT NULL,  PERIOD FOR application_time(valid_from, valid_to),  PRIMARY KEY(contract_id, valid_from)) ENGINE=InnoDB;INSERT INTO contract_price VALUES(7001,'2026-01-01','2026-07-01',100.00),(7001,'2026-07-01','2027-01-01',115.00);SELECT * FROM information_schema.PERIODSWHERE TABLE_SCHEMA='advanced21_l2' AND TABLE_NAME='contract_price';

The period columns must use compatible temporal types, and the start must precede the end. On MariaDB 11.4+, INFORMATION_SCHEMA.PERIODS exposes period metadata, making the temporal contract observable rather than inferred from column names.

3. Change only a portion of a period

UPDATE contract_priceFOR PORTION OF application_timeFROM '2026-08-01' TO '2026-10-01'SET monthly_price=120.00WHERE contract_id=7001;SELECT contract_id,valid_from,valid_to,monthly_priceFROM contract_priceWHERE contract_id=7001ORDER BY valid_from;

MariaDB can split the original period into segments around the modified portion. Verify the resulting rows rather than assuming a single row was updated. This behavior is powerful, but it also means application code that expects one-row update counts may need explicit tests.

4. Wrong approach: allow overlapping business truth

Two rows for the same contract that overlap in valid time can produce contradictory answers. A composite primary key on (contract_id, valid_from) prevents duplicate starts, but it does not by itself prevent one interval from overlapping another.

-- This may be structurally unique yet semantically conflicting:INSERT INTO contract_price VALUES(7001,'2026-09-15','2026-11-01',999.00);-- Detect overlap pairs explicitly if the schema does not enforce them:SELECT a.contract_id,       a.valid_from,a.valid_to,       b.valid_from,b.valid_toFROM contract_price aJOIN contract_price b  ON a.contract_id=b.contract_id AND (a.valid_from,a.valid_to) < (b.valid_from,b.valid_to) AND a.valid_from < b.valid_to AND b.valid_from < a.valid_to;

The portable overlap predicate is the key idea: two half-open intervals overlap when each starts before the other ends. Where your target MariaDB supports WITHOUT OVERLAPS, prefer a declarative key/constraint design and still test its exact semantics.

5. Enforce non-overlap on supported versions

DROP TABLE IF EXISTS contract_price_strict;CREATE TABLE contract_price_strict (  contract_id BIGINT NOT NULL,  valid_from DATE NOT NULL,  valid_to DATE NOT NULL,  monthly_price DECIMAL(10,2) NOT NULL,  PERIOD FOR application_time(valid_from, valid_to),  UNIQUE KEY uq_contract_period (contract_id, application_time WITHOUT OVERLAPS)) ENGINE=InnoDB;SHOW CREATE TABLE contract_price_strict\G

If the target server rejects this syntax, do not silently remove the constraint and pretend equivalence. Record the feature/version gap and implement an application/locking strategy with concurrency tests.

6. Build a bitemporal table

CREATE TABLE contract_price_bitemporal (  contract_id BIGINT NOT NULL,  valid_from DATE NOT NULL,  valid_to DATE NOT NULL,  monthly_price DECIMAL(10,2) NOT NULL,  row_start TIMESTAMP(6) AS ROW START INVISIBLE,  row_end   TIMESTAMP(6) AS ROW END INVISIBLE,  PERIOD FOR application_time(valid_from,valid_to),  PERIOD FOR system_time(row_start,row_end),  PRIMARY KEY(contract_id,valid_from)) ENGINE=InnoDB WITH SYSTEM VERSIONING;INSERT INTO contract_price_bitemporal(contract_id,valid_from,valid_to,monthly_price)VALUES (7002,'2026-01-01','2027-01-01',90.00);UPDATE contract_price_bitemporalSET monthly_price=95.00WHERE contract_id=7002;SELECT contract_id,valid_from,valid_to,monthly_price,ROW_START,ROW_ENDFROM contract_price_bitemporal FOR SYSTEM_TIME ALLWHERE contract_id=7002ORDER BY ROW_START;

Now one query can ask “what is valid for the customer on a business date?” while another asks “what did the database believe at a historical transaction time?” The two axes solve different disputes.

7. Lab acceptance and cleanup

SELECT contract_id, COUNT(*) AS row_segmentsFROM contract_priceGROUP BY contract_id;SELECT contract_id,valid_from,valid_to,monthly_priceFROM contract_priceWHERE contract_id=7001ORDER BY valid_from;DROP DATABASE advanced21_l2;

Production judgment and bridge to Lesson 3

Temporal modeling is valuable when “when” is part of the domain, not just metadata. Make interval boundaries, inclusivity, overlap rules, correction semantics and retention part of the schema contract. Lesson 3 switches from time semantics to dialect semantics: MariaDB can emulate a meaningful subset of Oracle behavior, but compatibility mode is a migration tool rather than a promise that Oracle applications run unchanged.

Bitemporal reasoning: valid time and system time answer different business questions

Application time (valid time) describes when the business fact is intended to be true. System time describes when the database stored a particular version. Combining them lets you distinguish “the price valid on 1 July” from “what our database believed on 1 July about the price valid on 1 July.” That distinction matters when facts arrive late, are corrected retroactively, or future-dated contracts are entered before they become effective.

Boundary design must be explicit. Choose whether periods are half-open such as [valid_from, valid_to) and use that convention everywhere so adjacent periods meet without overlapping. Open-ended contracts need a consistent representation. Overlap prevention should be enforced in data constraints where the target feature supports it, or through a transactional design that is tested under concurrency; an application-side “check then insert” is vulnerable to races.

A bitemporal query often has two predicates: one selecting the system-time snapshot and one selecting the business-valid period. Build tests with late-arriving corrections: insert a contract, change it retroactively, then ask both “what is valid now?” and “what did we believe yesterday?” If those questions return the same result in every test, the dataset is not exercising the reason bitemporal modeling exists.

Indexes and retention follow the query contract. Valid-time range searches need access paths on business key plus period boundaries; system history adds more versions and therefore more maintenance. Retaining all corrections forever may be correct for one domain and wasteful for another. Document who may correct historical business facts, what provenance is retained, and when history can be archived or deleted.

Constraint tests for valid-time schedules under concurrency

Use fixtures that try adjacent, overlapping, nested, duplicate, and open-ended periods for the same business key. The expected result should be written before running the insert so the learner can distinguish a modeled rule from whatever MariaDB happens to accept. If overlap is prohibited, test two sessions attempting conflicting periods concurrently; this reveals whether enforcement is truly in the database contract or only in a race-prone application pre-check.

When corrections are allowed, define how they are authorized and audited. A retroactive valid-time correction can legitimately rewrite what the business says was true without erasing system-time evidence of when the correction was recorded. That dual meaning is the core value of bitemporal design, but it also means application APIs need clear language such as “effective date” versus “recorded at” rather than exposing two timestamps with ambiguous names.

API naming is part of temporal correctness

Expose temporal concepts with domain-specific names such as effective_from, effective_to, and “recorded at” rather than generic start/end timestamps. Clear naming prevents callers from using system time as if it were business validity. Document whether an API asks for the current valid fact, the fact valid at a business date, or the database belief as of an earlier recording time.

Authoritative references

• MariaDB Documentation — Application-Time Periods
• MariaDB Documentation — Bitemporal Tables
• MariaDB Documentation — INFORMATION_SCHEMA.PERIODS