Effective Dating for Time-Bound Records in SQL

1. The Basic Idea: valid_from and valid_to as a Business-Controlled Interval

Effective dating adds two columns to a row that describe a period during which that row is applicable from a business standpoint. Unlike system-versioning, the database does not set these values automatically here; the application or a business user deliberately decides when a discount, a rate, or a contract term starts and ends. The database merely stores that decision, without checking it for business correctness on its own.

That simplicity is both a strength and a weakness: effective dating can be implemented with two DATE columns in any relational database without special features, but it requires the application to prevent overlaps itself unless the database is backed by additional constraints.

2. Why Non-Overlap Is a Critical Business Rule

Two simultaneously active prices for the same product, or two overlapping contract terms for the same customer, are rarely a harmless data glitch. They typically cause real business problems: duplicate discount grants, contradictory billing, or a system that returns two rows from a query where the application expects exactly one and consequently uses whichever row happens to come first, possibly the wrong one.

Such errors typically do not come from obviously wrong input, but from edge cases: an end date accidentally set one day too late, a new period that is not checked against existing periods when it is created, or a batch import that merges several sources without validation. Without an enforced rule in the database, the system relies entirely on the care taken at every individual write path.

3. Half-Open Intervals as a Convention Against Off-by-One Errors

A proven convention is the half-open interval: valid_from is inclusive, valid_to is exclusive, written as [from, to). A period from January 1st to January 31st then ends exactly on February 1st, not on January 31st at 23:59:59, and the following period can seamlessly start with valid_from set to February 1st, without risking a gap or overlap of a few seconds.

If a closed interval is used instead, where both boundaries are inclusive, off-by-one errors regularly appear when computing adjacent periods, especially with date-only values where the question of whether a given day still belongs to a period is easily answered inconsistently. Half-open intervals avoid that ambiguity from the ground up.

4. Constraint Strategies Against Overlapping Validity Periods

PostgreSQL offers the most robust solution with exclusion constraints on range types: a column of type daterange or tsrange, combined with a GIST index, prevents two overlapping periods for the same entity key from being inserted directly at the database level, regardless of which code path the insert comes from. A violation produces a clear error at write time, not later during some downstream query.

In databases without native range types, such as MySQL or SQL Server, the same guarantee can be recreated through a trigger that checks before every insert and update whether the new period overlaps an existing one, or more pragmatically through application logic inside the same transaction with an explicit SELECT ... FOR UPDATE lock on affected rows, to rule out race conditions from concurrent writes.

-- PostgreSQL: exclusion constraint against overlapping discount periods
CREATE TABLE discount_campaign (
    campaign_id  SERIAL PRIMARY KEY,
    product_id   INT NOT NULL,
    discount_pct NUMERIC(5,2) NOT NULL,
    period       DATERANGE NOT NULL,
    EXCLUDE USING GIST (product_id WITH =, period WITH &&)
);

-- This insert fails because the period overlaps an existing one
INSERT INTO discount_campaign (product_id, discount_pct, period)
VALUES (100, 10.00, daterange('2026-06-01', '2026-06-15', '[)'));

5. Practical Example: Time-Limited Discounts Without Duplicate Validity

For a product, several discount campaigns should be possible over the course of a year, but never two simultaneously active campaigns for the same item. The table from the example above enforces exactly that through the exclusion constraint: as soon as a marketing team tries to create a new campaign that overlaps an existing one, the database rejects the insert immediately, long before the mistake would become visible in production.

Querying the currently active discount then only needs a simple condition using the containment operator, which checks whether a given point in time falls within the stored period, without the usual two separate comparisons against valid_from and valid_to.

-- Find the currently active discount for a product
SELECT discount_pct
FROM discount_campaign
WHERE product_id = 100
  AND period @> CURRENT_DATE;

6. Practical Example: Contract Changes as a Chain of Effectively Dated Versions

Contract changes can be modeled as a chain of rows, where every new version automatically ends the period of the previous one. If a monthly contract fee changes effective July 1st, the existing row's valid_to is set to July 1st and a new row with valid_from set to July 1st and the new amount is inserted, within the same transaction, so that there is never a moment with zero or two applicable terms.

That chain can additionally be secured through a foreign key relationship to the previous version, making the full change history of a contract traceable without relying on a separate history table, though without the automatic guarantee that system-versioning would provide for transaction time.

-- Close the existing contract term as of the effective date
UPDATE contract_term
SET valid_to = '2026-07-01'
WHERE contract_id = 55 AND valid_to IS NULL;

-- Insert the new term starting on the same effective date
INSERT INTO contract_term (contract_id, monthly_fee_cents, valid_from, valid_to)
VALUES (55, 4990, '2026-07-01', NULL);

7. Reliably Querying the Currently Valid Record

Without range types, the currently valid record is found through two conditions: valid_from less than or equal to the reference date, and valid_to either NULL, marking an open end, or greater than the reference date. This formulation should be used consistently everywhere in the codebase, since small deviations, such as forgotten NULL handling, lead to inconsistent results between different queries against the same table.

The most robust way to handle this is to encapsulate it in a view or a database function that defines this logic once centrally, instead of repeating it in every individual query in application code and risking contradictory implementations.

-- Encapsulate the currently valid contract term in a view
CREATE VIEW contract_term_current AS
SELECT *
FROM contract_term
WHERE valid_from <= CURRENT_DATE
  AND (valid_to IS NULL OR valid_to > CURRENT_DATE);

8. Indexing for Performant Effective-Dating Queries

For range types in PostgreSQL, a GIST index is the natural choice, efficiently supporting both containment and overlap checks, and it can be the same index already required for the exclusion constraint. For the classic two-column variant with valid_from and valid_to, a composite index on both columns helps, though how NULL in valid_to is handled by the index varies by database, and a sentinel value such as the year 9999 is sometimes preferable to NULL.

SQL Server can additionally use a filtered index that covers only the currently valid rows, meaning those with an open or future valid_to, which noticeably speeds up queries against the current state without having to index the entire history as well.

9. Distinguishing From System-Versioning: Business-Controlled, Not Automatic

Effective dating and system-versioning look similar at first glance but differ fundamentally in responsibility: with effective dating, business logic deliberately decides the start and end of a period, while system-versioning automatically creates a new version on every technical change, regardless of whether anything actually changed from a business perspective.

In practice, both concepts can be combined: a table with effective-dating columns for business validity can additionally be system-versioned, making technical changes to that business validity traceable as well, exactly the pattern covered in depth in the article on bitemporal modeling.

10. Summary

11. FAQ: Effective Dating at a Glance