Skip to content

Expedition. Free, virtual, Nov 3–6.

Technical tracks for practitioners, outcomes for leaders.

Register now
Snowflake for Developers/Developer Blog/Working with Time in Snowflake

Working with Time in Snowflake

Elizabeth Christensen

One of my favorite SQL features is all the ways you can store and query time. Since humans first started recording data, they've been keeping track of time. There are so many options, so many ways to think about it, and it reminds me how special time is to us as humans.

Snowflake has always had solid timestamp support and 2026 brings two big additions to time support: an INTERVAL column type and a PERIOD type for storing and querying time ranges. First I want to show you the new time fields in Snowflake and then I'll do an overview of time support that you need to know for working with the Snowflake data. This is a super long blog post, sorry, but what can I say, I love time in SQL.

Storing Time Ranges with PERIOD

All the examples in this post use a train schedule dataset to play with. You can grab the companion SQL file and paste it into a Snowsight worksheet to follow along.

Snowflake's PERIOD is the big addition to time handling, giving you one column that stores both a start and an end as a proper [begin, end) value.

The common pattern before PERIOD was two separate time columns:

-- The old way
CREATE TABLE route_assignments_old (
    conductor_id  INT,
    route         TEXT,
    valid_from    TIMESTAMP_LTZ NOT NULL,
    valid_to      TIMESTAMP_LTZ
);

Then to find a conductor's current route, you'd write:

SELECT route FROM route_assignments_old
WHERE conductor_id = 1
  AND valid_from <= CURRENT_TIMESTAMP
  AND (valid_to > CURRENT_TIMESTAMP OR valid_to IS NULL);

With PERIOD

With the PERIOD type you collapse the two columns into one and get a whole function library for querying it.

CREATE TABLE route_assignments (
    conductor_id  INT,
    conductor     TEXT,
    route         TEXT,
    assignment    PERIOD(TIMESTAMP_LTZ)
);

One function call with PERIOD_CONTAINS replaces the two-column comparisons:

SELECT conductor, route
FROM route_assignments
WHERE conductor_id = 1
  AND PERIOD_CONTAINS(assignment, CURRENT_TIMESTAMP);

Half-open semantics: [begin, end)

PERIOD uses half-open intervals: the begin is inclusive, the end is exclusive. This means adjacent assignments share an endpoint but don't overlap. There are no gaps, no double-counting.

We can find all clean handoffs with PERIOD_MEETS, and confirm they don't overlap with PERIOD_OVERLAPS:

-- Find conductors who moved directly from one route to another
SELECT a.conductor, a.route AS from_route, b.route AS to_route,
    PERIOD_END(a.assignment) AS handoff_date
FROM route_assignments a
JOIN route_assignments b
  ON a.conductor_id = b.conductor_id
  AND a.route != b.route
  AND PERIOD_MEETS(a.assignment, b.assignment)
WHERE PERIOD_END(a.assignment) < '9999-01-01'::TIMESTAMP_LTZ;
-- Maria Santos: Milwaukee → Detroit on 2025-06-01
-- Anika Patel:  Milwaukee → Minneapolis on 2026-03-01

[begin, end) is the standard for time ranges in databases — it makes contiguous periods easy to reason about.

PERIOD functions

Period also comes with a lot of functions to make querying really easy. Here's a peak fares table that uses PERIOD(DATE) to store pricing windows:

CREATE TABLE peak_fares (
    peak_name   TEXT,
    peak_times  PERIOD(DATE),
    fare_bump   INT
);

PERIOD_CONTAINS — is a point inside a period?

-- Is July 4th during a peak fare period?
SELECT peak_name, fare_bump
FROM peak_fares
WHERE PERIOD_CONTAINS(peak_times, DATE '2026-07-04');
-- Returns 'summer' with fare_bump 25

PERIOD_BEGIN / PERIOD_END — extract the endpoints

SELECT peak_name,
    PERIOD_BEGIN(peak_times) AS starts,
    PERIOD_END(peak_times)   AS ends
FROM peak_fares;

PERIOD_OVERLAPS — do two periods share any time?

-- Does a travel window hit a peak period?
SELECT peak_name, fare_bump
FROM peak_fares
WHERE PERIOD_OVERLAPS(peak_times, PERIOD(DATE) '[2026-12-22, 2026-12-27)');
-- Returns 'holiday' with fare_bump 50

PERIOD_MEETS — does one end exactly where the other begins?

-- Summer ends Sep 1, fall_foliage begins Sep 1 — adjacent, no gap.
SELECT a.peak_name || ' → ' || b.peak_name AS adjacent_peaks,
    PERIOD_END(a.peak_times) AS boundary
FROM peak_fares a
JOIN peak_fares b
  ON a.peak_name != b.peak_name
  AND PERIOD_MEETS(a.peak_times, b.peak_times)
WHERE PERIOD_BEGIN(a.peak_times) < PERIOD_BEGIN(b.peak_times);
-- summer → fall_foliage at 2026-09-01

PERIOD_INTERSECT — what's the overlapping portion?

-- A trip spans Aug 25 to Sep 10 — which peak fare days apply?
SELECT peak_name,
    PERIOD_INTERSECT(peak_times, PERIOD(DATE) '[2026-08-25, 2026-09-10)') AS overlap_window
FROM peak_fares
WHERE PERIOD_OVERLAPS(peak_times, PERIOD(DATE) '[2026-08-25, 2026-09-10)');
-- summer:       [2026-08-25, 2026-09-01)
-- fall_foliage: [2026-09-01, 2026-09-10)

You can construct a PERIOD on the fly from two timestamp columns using PERIOD_CONSTRUCT, which is useful in WHERE clauses without changing your table schema:

-- Find train trips running during a specific window
SELECT trip_id, train_number
FROM train_schedule
WHERE PERIOD_OVERLAPS(
    PERIOD_CONSTRUCT(actual_departure, actual_arrival),
    PERIOD(TIMESTAMP_LTZ) '[2026-04-29 13:00:00 +00:00, 2026-04-29 14:00:00 +00:00)'
);
-- Returns trips 2 (ACE-205, Chicago → Detroit) and 3 (ACE-310, Chicago → St. Louis)

This is handy when you have existing start/end columns since you can now use PERIOD query semantics without migrating the table.

PERIOD vs INTERVAL: two different things

PERIOD and INTERVAL look related but they're different:

  • PERIOD is an anchored range on the timeline with a specific start and end. [2026-06-01, 2026-09-01) means those exact dates.
  • INTERVAL is an unanchored duration, 3 months has no specific start, it's just a length. You add it to a timestamp to get somewhere specific.
-- PERIOD: a named span of time
PERIOD(DATE) '[2026-06-01, 2026-09-01)'

-- INTERVAL: a duration with no fixed position
INTERVAL '3 months'

-- Combine them: add a duration to a date to get an endpoint
SELECT DATE '2026-06-01' + INTERVAL '3 months';
-- 2026-09-01

INTERVAL: Query and store durations

Snowflake's INTERVAL type expresses durations like 3 hours, 30 days, 1 year 6 months and you can use them in time arithmetic.

-- What's departing in the next 2 hours?
SELECT * FROM train_schedule
WHERE actual_departure BETWEEN CURRENT_TIMESTAMP
    AND CURRENT_TIMESTAMP + INTERVAL '2 hours';

There are two families:

  • INTERVAL YEAR TO MONTH — for calendar durations: years, months, quarters
  • INTERVAL DAY TO SECOND — for precise durations: days, hours, minutes, seconds, fractions

Interval as a data type

New in 2026 and now in preview: INTERVAL can now be used as a column data type. Previously it was only valid in expressions, you couldn't store a duration in a table. Now you can.

CREATE TABLE train_passes (
    pass_id    INT,
    passenger  TEXT,
    pass_type  TEXT,
    purchased  TIMESTAMP_LTZ,
    duration   INTERVAL YEAR TO MONTH
);

With the duration stored in the table, you can calculate expiration directly. The display format uses year-month notation: +1-00 is 1 year, +0-03 is 3 months. Inserts will look something like this:

-- INTERVAL values need INSERT-SELECT with cast syntax:
-- '1-0' = 1 year 0 months, '0-3' = 0 years 3 months, etc.
INSERT INTO train_passes
SELECT 1, 'Alice',   'annual',    '2026-01-15 00:00:00 +00:00'::TIMESTAMP_LTZ, '1-0'::INTERVAL YEAR TO MONTH;
INSERT INTO train_passes

And you can set a date but your INTERVAL duration to get the expiry date.

NOTE: INTERVAL literals aren't supported in a VALUES clause yet, so use INSERT-SELECT with a cast instead.

SELECT passenger, pass_type, purchased,
    purchased + duration AS expires
FROM train_passes;

Data Types for Time

Now for the foundation, let's look at Snowflake's other features for storing time values.

DATE — date only, no time. 2026-09-15.

TIME — time of day only, no date. 14:23:58.560. Rarely useful on its own since time almost always needs a date alongside it.

TIMESTAMP — the full date and time.

Choosing the Right TIMESTAMP

Snowflake has three timestamp variants, and picking the wrong one causes silent problems in multi-timezone environments:

TIMESTAMP_NTZ (No Time Zone) — stores exactly what you give it, no timezone math. 2026-09-15 14:23:58 -0700 goes in, 2026-09-15 14:23:58 comes out — the offset is dropped. This is the Snowflake default (a bare TIMESTAMP maps here unless you change TIMESTAMP_TYPE_MAPPING). Use it when your data is already in UTC and you just need wall-clock storage.

TIMESTAMP_LTZ (Local Time Zone) — stores UTC internally, displays in your session's TIMEZONE setting. This is the closest equivalent to something like Postgres's TIMESTAMPTZ. If you insert 2026-09-15 14:23:58 -0700 with a session set to UTC, you get 2026-09-15 21:23:58 +00:00 back. The database handles the math. Personally I think this is the way to go with timezone storage.

TIMESTAMP_TZ (Time Zone) — stores both the UTC value and the offset that was present at insert time. Unlike TIMESTAMP_LTZ, it doesn't recalculate for DST later — the original offset is frozen in. Useful for audit logs where you need to preserve the exact local time something was recorded.

Timestamp Precision: Down to Nanoseconds

All three timestamp variants (NTZ, LTZ, TZ) store up to 9 decimal places of precision — 9 means nanoseconds. The default is 9. If you're coming from Postgres, this is a step up: Postgres timestamps max out at microseconds (6 decimal places, TIMESTAMP(6)).

CREATE TABLE sensor_readings (
    reading_id   INT,
    recorded_at  TIMESTAMP_LTZ(9),
    value        FLOAT
);

One thing to watch: most client libraries and tools only surface millisecond precision (3 digits) regardless of what's stored. You can use DATE_PART to prove the full precision is there. If you're ingesting from a system that genuinely produces sub-millisecond data like telemetry, trading systems, scientific instruments, you can take advantage of the precision.

Time Formats and Functions

I'm an American midwesterner, so I'd naturally write Bastille Day as July 14th, 1789. ISO 8601 says it's 1789-07-14 17:30:00.000 — year-month-day-time. That's what Snowflake uses internally and what you should use for inserts and comparisons.

For output, TO_CHAR gives you full control of how to view data:

SELECT trip_id, train_number,
    TO_CHAR(actual_departure, 'DY, Mon DD, YYYY HH24:MI:SS TZH:TZM') AS formatted_departure,
    TO_CHAR(actual_departure, 'MM/DD/YYYY') AS short_date
FROM train_schedule
WHERE trip_id <= 3;
-- Wed, Apr 29, 2026 08:03:00 +00:00  |  04/29/2026
-- Wed, Apr 29, 2026 10:15:00 +00:00  |  04/29/2026
-- Wed, Apr 29, 2026 12:22:00 +00:00  |  04/29/2026

Parsing strings back to timestamps:

SELECT TO_TIMESTAMP('Apr 29 2026 8:03AM', 'Mon DD YYYY HH:MIPM');

Time Zones

To convert to a specific timezone:

SELECT trip_id, route,
    actual_departure,
    CONVERT_TIMEZONE('America/Chicago', actual_departure) AS chicago_time,
    CONVERT_TIMEZONE('Asia/Tokyo', actual_departure) AS tokyo_time
FROM train_schedule
WHERE trip_id <= 3;

Snowflake uses IANA timezone names.

Date and Time Arithmetic

DATEADD and DATEDIFF are the workhorses for shifting and measuring time.

Using the train schedule table:

-- How many minutes late was each train?
SELECT
    trip_id, train_number,
    DATEDIFF('minute', scheduled_arrival, actual_arrival) AS minutes_late
FROM train_schedule
ORDER BY minutes_late DESC;

-- 2 hours before each actual arrival
SELECT
    trip_id,
    DATEADD('hour', -2, actual_arrival) AS two_hours_before_arrival
FROM train_schedule;

DATEDIFF units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond.

You can also subtract two timestamps directly, which returns an INTERVAL. Wrap with DATEDIFF('second', scheduled_arrival, actual_arrival) if you need a numeric value.:

SELECT AVG(DATEDIFF('second', scheduled_arrival, actual_arrival)) AS avg_delay_seconds
FROM train_schedule;

Rolling Up Time Data with DATE_TRUNC

DATE_TRUNC is the go-to for aggregating time-series data by day, week, or month. Even though the source column is a full TIMESTAMP_LTZ, DATE_TRUNC collapses it to just the day boundary.

SELECT
    DATE_TRUNC('day', actual_departure) AS departure_day,
    COUNT(*) AS trip_count
FROM train_schedule
GROUP BY departure_day
ORDER BY departure_day;

DEPARTURE_DAY               | TRIP_COUNT
2026-04-29 00:00:00.000 +00 | 3
2026-04-30 00:00:00.000 +00 | 3
2026-05-01 00:00:00.000 +00 | 2

Units: year, quarter, month, week, day, hour, minute, second. The output is always a timestamp truncated to the boundary you specified.

One gotcha: week defaults to Sunday as the first day. If your business week starts Monday, set WEEK_START = 1 at the session level before using DATE_TRUNC('week', ...).

TL;DR

Thanks for spending your time learning about time.

  • PERIOD is new and stores time ranges like schedules, subscriptions, validity windows. Use PERIOD_CONTAINS, PERIOD_OVERLAPS, PERIOD_MEETS, and PERIOD_INTERSECT instead of two-column logic.
  • INTERVAL as a column type is new too which can store durations directly instead of converting to seconds or splitting into numeric components.
  • TIMESTAMP_LTZ is your best friend for most timestamp use cases. This stores as UTC, displayed in your local zone.
  • Snowflake timestamps default to nanosecond precision, but verify your source data actually has that resolution.
  • DATEADD and DATEDIFF for arithmetic; INTERVAL for inline duration expressions.
  • DATE_TRUNC for bucketing by day, week, month. Watch the WEEK_START setting.

Related Resources

Updated Oct 16, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances