Time and Date Functions

TL;DR

Time data is where SQL hurts most often: timezones, leap seconds, DST, “what does Monday mean”, date vs timestamp vs timestamptz. Get the fundamentals right and most pain disappears.

The Postgres rules of thumb:

• Always store timestamps as timestamptz (timestamp with time zone), not timestamp. timestamptz stores UTC internally and renders in your session timezone; timestamp stores wall-clock with no timezone, which is almost never what you want.
• Bucket with DATE_TRUNC('day', ts) for histograms.
• Range filters with >= and <, never BETWEEN for dates — BETWEEN is inclusive on both sides and produces edge-case bugs.
• Rolling windows use RANGE BETWEEN INTERVAL '...' PRECEDING AND CURRENT ROW — see SQL 201.

Types in Postgres (and dialect notes)

In Snowflake, TIMESTAMP_TZ ≈ Postgres timestamptz. BigQuery distinguishes TIMESTAMP (UTC) and DATETIME (naive); use TIMESTAMP. MySQL has DATETIME (naive) and TIMESTAMP (UTC, but auto-magic and weird) — pick DATETIME and store UTC strings.

The functions worth memorizing

A worked example — daily / weekly / monthly histograms

CREATE TABLE events (id int, user_id int, ts timestamptz, kind text);
-- assume populated with millions of rows

-- Events per day, last 30 days
SELECT DATE_TRUNC('day', ts AT TIME ZONE 'UTC')::date AS day,
       COUNT(*) AS events
FROM   events
WHERE  ts >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY day
ORDER  BY day;

-- Events per ISO week
SELECT DATE_TRUNC('week', ts) AS week_start,
       COUNT(*) AS events
FROM   events
WHERE  ts >= CURRENT_DATE - INTERVAL '90 days'
GROUP  BY week_start;

-- Events per month
SELECT DATE_TRUNC('month', ts)::date AS mo,
       COUNT(*) AS events
FROM   events
WHERE  ts >= CURRENT_DATE - INTERVAL '12 months'
GROUP  BY mo;

DATE_TRUNC('week', ...) truncates to ISO week-start (Monday). That’s usually right for analytics; if you want Sunday-start, do DATE_TRUNC('week', ts + INTERVAL '1 day') - INTERVAL '1 day'.

BigQuery / Snowflake equivalents. BigQuery: TIMESTAMP_TRUNC(ts, DAY). Snowflake: DATE_TRUNC('DAY', ts). Same idea, slightly different spellings.

The BETWEEN trap for date ranges

-- WRONG: misses events at exactly 2026-04-30 23:59:59.001 (or later that day)
SELECT * FROM events WHERE ts BETWEEN '2026-04-01' AND '2026-04-30';

BETWEEN is inclusive on both ends, but '2026-04-30' is interpreted as 2026-04-30 00:00:00. So events from later that day are excluded. Always use >= start and < next-day:

SELECT * FROM events
WHERE  ts >= '2026-04-01'
  AND  ts <  '2026-05-01';     -- next day, exclusive

This is also the only form that uses an index efficiently — BETWEEN on day-truncated dates may not.

Time zones — the one rule

Store UTC. Convert at the edges.

-- Bad: filtering with a wall-clock time, ambiguous near DST
WHERE ts AT TIME ZONE 'America/New_York' >= '2026-03-08 02:30'

-- Good: convert the boundary to UTC, filter against UTC storage
WHERE ts >= '2026-03-08 07:30+00'

When you need to bucket by local day (e.g., “events per local day per user”), convert at the bucket step:

SELECT user_id,
       DATE_TRUNC('day', ts AT TIME ZONE u.timezone) AS local_day,
       COUNT(*) AS n
FROM   events e
JOIN   users  u ON u.id = e.user_id
GROUP  BY user_id, local_day;

Each user’s local day is computed against their timezone. Queries that forget this aggregate everyone into one global day, which is wrong for churn windows, daily-active-users, etc.

Generating a date spine

Most rolling-feature queries need a row per day even when no events happened. Postgres has generate_series:

SELECT d::date AS day
FROM   generate_series('2026-04-01'::date, '2026-04-30', '1 day') d;

Cross-join with users to build per-user-per-day spines (see JOINs (SQL 102) for the pattern). BigQuery has GENERATE_DATE_ARRAY('2026-04-01', '2026-04-30') and UNNEST. Snowflake’s idiom uses LATERAL FLATTEN or ROW_NUMBER over a fact table.

Durations — interval arithmetic

-- Time spent on each session
SELECT session_id,
       MIN(ts)                               AS started,
       MAX(ts)                               AS ended,
       MAX(ts) - MIN(ts)                     AS duration,
       EXTRACT(EPOCH FROM MAX(ts) - MIN(ts)) AS duration_seconds
FROM   events
GROUP  BY session_id;

EXTRACT(EPOCH FROM interval) converts to seconds — usually what you want in features. EXTRACT(EPOCH FROM x) on a timestamptz returns Unix epoch seconds.

Rolling windows over time

The right tool is a window function with a range frame (not row frame):

SELECT user_id, day, daily_spend,
       SUM(daily_spend) OVER (
           PARTITION BY user_id
           ORDER BY day
           RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
       ) AS spend_7d
FROM   user_daily_spend;

RANGE BETWEEN INTERVAL '6 days' PRECEDING … honors actual calendar distance, so a missing day doesn’t quietly slide the window to “8 days” the way ROWS BETWEEN 6 PRECEDING … would. See SQL 201 for the full frame discussion.

Common pitfalls

• timestamp (without time zone) for events. Almost never what you want — use timestamptz.
• BETWEEN for date ranges. Use >= and <.
• Local-time string literals near DST boundaries. Ambiguous (the “fall back” hour exists twice). Always work in UTC.
• DATE_TRUNC('week', ...) assumed to start on Sunday. Postgres default is Monday (ISO).
• EXTRACT(DOW FROM ...) returns 0=Sunday, 6=Saturday in Postgres. ISODOW returns 1=Monday, 7=Sunday. Check before using as a feature.
• Comparing interval with interval containing months. INTERVAL '30 days' ≠ INTERVAL '1 month' — months have variable length. Postgres interval comparisons can surprise.
• Mixing date and timestamptz. Postgres casts up; the cast may not be what you want. Be explicit.

Production patterns for ML

1. Daily / weekly cohort tables. A standard analytics output:

SELECT DATE_TRUNC('week', signed_up_at)::date AS cohort_week,
       DATE_TRUNC('week', activity_at)::date  AS active_week,
       COUNT(DISTINCT user_id) AS active_users
FROM   user_activity
GROUP  BY cohort_week, active_week
ORDER  BY cohort_week, active_week;

This produces a long-format cohort retention table, ready for a heatmap visualization.

2. Time-of-day / day-of-week features. Useful for any time-aware model:

SELECT event_id,
       EXTRACT(HOUR FROM ts AT TIME ZONE u.timezone)   AS local_hour,
       EXTRACT(ISODOW FROM ts AT TIME ZONE u.timezone) AS local_dow,
       SIN(2 * PI() * EXTRACT(HOUR FROM ts) / 24)      AS hour_sin,  -- cyclical encoding
       COS(2 * PI() * EXTRACT(HOUR FROM ts) / 24)      AS hour_cos
FROM   events e JOIN users u ON u.id = e.user_id;

Sine/cosine encodings of cyclical features (hour, day of week, month) are a standard trick — gradient-boosted trees can use the raw integer, neural nets benefit from the sin/cos pair.

Resources

• PostgreSQL — date/time functions — postgresql.org/docs
• PostgreSQL — date/time types — postgresql.org/docs
• TimescaleDB — time-series in Postgres — docs.timescale.com
• PostgreSQL documentation — postgresql.org/docs
• Use The Index, Luke — use-the-index-luke.com — date-range index usage.