Query Time Ranges With Snowflake’s PERIOD Data Type

Use native temporal ranges to look up effective records, join histories and allocate fleet operating hours.

A rate changes on April 1. A report for February should still use the old rate. With separate start and end columns, every query has to reconstruct that rule, including whether the end date counts.

Snowflake’s PERIOD data type stores those bounds as one typed value. Its SQL functions let you ask whether a date falls within a range, whether two ranges overlap and which part they share. PERIOD and its functions are generally available.

The examples below show three applications: effective-date lookups, historical joins and fleet duty-time reporting.

A time range with explicit boundaries

An INTERVAL describes a duration, such as eight hours. A PERIOD identifies a range with a beginning and an end, such as a shift from Sunday at 10 p.m. to Monday at 6 a.m.

Declare the element type explicitly: PERIOD(DATE), PERIOD(TIME) or a timestamp variant such as PERIOD(TIMESTAMP_NTZ). Snowflake also supports TIMESTAMP_LTZ and TIMESTAMP_TZ bounds, with fractional-second precision from 0 to 9 for time and timestamp types.

You can construct a period from two values of the same temporal type:

SELECT PERIOD_CONSTRUCT(
    DATE '2026-01-01', DATE '2026-04-01'
) AS valid_during;

The result is [2026–01–01, 2026–04–01). The opening bracket includes January 1; the closing parenthesis excludes April 1. Every PERIOD uses this half-open convention. Consecutive ranges can meet at the same boundary without overlapping.

Both bounds must be finite, and the beginning must precede the end. Empty or reversed ranges are invalid. A NULL bound does not mean “still active.” For ongoing records, choose a documented finite ending-bound convention appropriate to your application.

Find the version effective on a business date

Reference data often has an effective date that differs from its arrival date. A rate loaded today might apply next month. A correction received next week might apply to a date in the past.

For an effective-date lookup, store each version’s business-validity range alongside its value. This example has a 5% rate through March and a 6% rate starting April 1:

WITH rate_history AS (
    SELECT 'RATE_A' AS rate_id, 0.05 AS rate,
           PERIOD(DATE) '[2026-01-01, 2026-04-01)' AS valid_during
    UNION ALL
    SELECT 'RATE_A', 0.06,
           PERIOD(DATE) '[2026-04-01, 2027-01-01)'
)
SELECT rate_id, rate
FROM rate_history
WHERE PERIOD_CONTAINS(valid_during, DATE '2026-02-15');

The query returns RATE_A with 0.05. Change the lookup date to April 1 and it returns 0.06. The boundary belongs only to the new version.

This pattern also applies to effective-dated premiums or contract terms. Your ingestion logic still creates and closes versions. PERIOD validates each range, but it does not prevent overlapping versions for the same business key.

Join histories on the same business date

A contract’s status and overdue balance can change on different days. A historical report needs the version of each that applied on its reporting date. Joining their latest rows would answer a different question.

Assume contract_status and overdue_balances each have a valid_during column of type PERIOD(DATE). The following query aligns both histories to September 16:

WITH reporting_date AS (
    SELECT DATE '2026-09-16' AS business_date
)
SELECT status_history.contract_id, report.business_date,
       status_history.status, balance_history.overdue_amount
FROM reporting_date AS report
JOIN contract_status AS status_history
  ON PERIOD_CONTAINS(status_history.valid_during, report.business_date)
LEFT JOIN overdue_balances AS balance_history
  ON balance_history.contract_id = status_history.contract_id
 AND PERIOD_CONTAINS(balance_history.valid_during, report.business_date);

For a contract marked ACTIVE throughout September with an overdue balance of 250 in [2026-09-10, 2026-09-20), the September 16 report returns ACTIVE and 250. If no balance version covers that date, the left join preserves the contract and returns NULL for the unmatched balance.

Each history should have at most one applicable version per key and date. If you also track when information was recorded, filter both histories for the chosen recorded-time snapshot. Your pipeline remains responsible for capturing that history. Replacing bound comparisons with PERIOD predicates improves readability; any performance benefit needs workload-specific testing.

Allocate fleet duty time across reporting weeks

A driver works from Sunday at 10 p.m. until Monday at 6 a.m. With a Monday-start reporting week, two hours belong to the previous week and six to the new one. Assigning all eight hours to the shift’s start date misstates both weekly totals.

Represent the duty window and each reporting week as timestamp periods, then intersect them. This query calculates the contribution to the new week:

WITH windows AS (
    SELECT
        PERIOD(TIMESTAMP_NTZ)
          '[2026-09-13 22:00:00, 2026-09-14 06:00:00)' AS duty,
        PERIOD(TIMESTAMP_NTZ)
          '[2026-09-14 00:00:00, 2026-09-21 00:00:00)' AS workweek
), allocation AS (
    SELECT PERIOD_INTERSECT(duty, workweek) AS segment
    FROM windows
)
SELECT DATEDIFF('second', PERIOD_BEGIN(segment), PERIOD_END(segment))
           / 3600.0 AS hours_in_workweek
FROM allocation;

The result is 6. Intersecting with the previous week returns 2, preserving all eight hours. For a fleet-wide report, join duty records to every reporting week they overlap before summing the allocated hours.

These totals can feed overtime-exposure reporting and staffing decisions. The same intersection pattern can measure how much of a vehicle’s maintenance window falls inside a planned operating window.

The example assumes a single, consistent timestamp basis without an offset change. Production systems need an explicit time-zone policy and authoritative duty records. Trip telemetry alone does not establish paid time or hours-of-service compliance; those rules remain separate from range allocation. Here, DATEDIFF measures seconds between the extracted bounds. It is not a PERIOD-to-INTERVAL conversion.

Start with one range query

Choose an existing effective-date lookup or overlap calculation and compare it with the equivalent PERIOD expression. This also gives migrations a native target for supported range operations previously represented with strings or split bounds. Validate shared boundaries and missing history: inclusive BETWEEN predicates need particular care when moving to exclusive ending bounds.

Use the PERIOD documentation for supported types and limitations, and the function reference for examples of each operation.

Query Time Ranges With Snowflake’s PERIOD Data Type was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.