Period functions¶

Snowflake provides scalar functions for constructing, inspecting, comparing, and combining PERIOD values. PERIOD values are half-open ranges [begin, end): the beginning bound is inclusive and the ending bound is exclusive.

Unless noted otherwise:

• Both PERIOD arguments must have the same element type (for example, both PERIOD(DATE)).
• A NULL input produces a NULL result.
• Predicate functions return BOOLEAN.
• Set-operation functions return PERIOD (or NULL when the result would be empty).

Constructor and accessors¶

• PERIOD_CONSTRUCT
• PERIOD_BEGIN
• PERIOD_END

Boolean predicates¶

• PERIOD_OVERLAPS
• PERIOD_CONTAINS
• PERIOD_EQUALS
• PERIOD_PRECEDES
• PERIOD_SUCCEEDS
• PERIOD_IMMEDIATELY_PRECEDES
• PERIOD_IMMEDIATELY_SUCCEEDS
• PERIOD_MEETS

Set operations¶

• PERIOD_INTERSECT
• PERIOD_LDIFF
• PERIOD_RDIFF

Examples¶

Because PERIOD values are half-open, two back-to-back periods meet without overlapping. The earlier period doesn’t contain the shared boundary instant (its exclusive ending bound), but the later period does (its inclusive beginning bound):

WITH periods AS (
  SELECT
    PERIOD(DATE) '[2024-01-01, 2024-04-01)' AS p1,
    PERIOD(DATE) '[2024-04-01, 2024-07-01)' AS p2
)
SELECT
    PERIOD_OVERLAPS(p1, p2) AS overlaps,
    PERIOD_MEETS(p1, p2) AS meets,
    PERIOD_CONTAINS(p1, DATE '2024-04-01') AS p1_contains_boundary,
    PERIOD_CONTAINS(p2, DATE '2024-04-01') AS p2_contains_boundary
  FROM periods;

+----------+-------+----------------------+----------------------+
| OVERLAPS | MEETS | P1_CONTAINS_BOUNDARY | P2_CONTAINS_BOUNDARY |
|----------+-------+----------------------+----------------------|
| False    | True  | False                | True                 |
+----------+-------+----------------------+----------------------+

For a full table-based walkthrough that stores, queries, orders, and combines PERIOD values, see Examples for the PERIOD data type.

List of functions¶