Post Thread

#Snowflake’s PERIOD data type represents a time range with explicit start and end boundaries, making it easier to query effective dates, detect overlapping ranges, and join historical records based on a specific date. It can also be used to intersect time ranges, such as splitting a work shift across reporting periods, while consistently handling boundaries and reducing complex date comparisons

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;

https://medium.com/snowflake/query-time-ranges-with-snowflakes-period-data-type-ce229d2306bd