SQL Date Functions: DATE_TRUNC, EXTRACT, DATEDIFF (2026)

-- DATE_TRUNC rounds a timestamp DOWN to the specified precision.
-- 2024-03-15 14:30:00 → DATE_TRUNC('month', ...) → 2024-03-01 00:00:00

-- PostgreSQL / Snowflake / Redshift: DATE_TRUNC(precision, timestamp)
SELECT DATE_TRUNC('month', '2024-03-15 14:30:00'::timestamp);
-- 2024-03-01 00:00:00

SELECT DATE_TRUNC('week', '2024-03-15'::date);
-- 2024-03-11 (Monday of that week in Postgres ISO)

SELECT DATE_TRUNC('quarter', '2024-08-20'::date);
-- 2024-07-01

SELECT DATE_TRUNC('year', '2024-08-20'::date);
-- 2024-01-01

-- BigQuery reverses argument order: DATE_TRUNC(date, part)
SELECT DATE_TRUNC('2024-03-15', MONTH);  -- 2024-03-01

-- MySQL has NO DATE_TRUNC. Use DATE_FORMAT to truncate:
SELECT DATE_FORMAT('2024-03-15', '%Y-%m-01');  -- '2024-03-01'

-- The most common pattern: group time series by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count,
  SUM(revenue) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- EXTRACT pulls ONE numeric component from a date or timestamp.

SELECT EXTRACT(YEAR FROM '2024-03-15'::date);     -- 2024
SELECT EXTRACT(MONTH FROM '2024-03-15'::date);    -- 3
SELECT EXTRACT(DAY FROM '2024-03-15'::date);      -- 15
SELECT EXTRACT(HOUR FROM '2024-03-15 14:30:00'::timestamp);  -- 14
SELECT EXTRACT(DOW FROM '2024-03-15'::date);      -- 5 (Friday, 0=Sun in Postgres)
SELECT EXTRACT(ISODOW FROM '2024-03-15'::date);   -- 5 (Friday, 1=Mon)
SELECT EXTRACT(EPOCH FROM '2024-03-15'::date);    -- 1710460800 (unix)

-- Find peak hours
SELECT
  EXTRACT(HOUR FROM event_time) AS hour_of_day,
  COUNT(*) AS event_count
FROM events
GROUP BY 1
ORDER BY 2 DESC;

-- Day-of-week distribution
SELECT
  EXTRACT(ISODOW FROM order_date) AS day_of_week,
  CASE EXTRACT(ISODOW FROM order_date)
    WHEN 1 THEN 'Monday'  WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday'
    WHEN 7 THEN 'Sunday'
  END AS day_name,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- CLASSIC TRAP: don't use EXTRACT(MONTH ...) to group time series.
-- EXTRACT returns just the month number (1-12). Jan 2024 and Jan 2025
-- both become 1 and get aggregated together. Use DATE_TRUNC for grouping.
-- EXTRACT is for cross-year analysis ("which month has the most sales").

-- Calculating the difference between two dates varies more across
-- databases than any other date function. Memorize the argument order.

-- PostgreSQL: subtract dates directly, returns INTEGER days
SELECT '2024-03-15'::date - '2024-01-01'::date;  -- 74

-- PostgreSQL: subtract timestamps, returns INTERVAL
SELECT '2024-03-15 14:00'::timestamp - '2024-03-15 10:30'::timestamp;
-- '03:30:00'

-- Snowflake / Redshift: DATEDIFF(unit, start, end)
SELECT DATEDIFF('day',   '2024-01-01', '2024-03-15');  -- 74
SELECT DATEDIFF('month', '2024-01-01', '2024-03-15');  -- 2
SELECT DATEDIFF('hour',  '2024-03-15 10:00', '2024-03-15 14:30');  -- 4

-- BigQuery: DATE_DIFF(end, start, unit) — different argument order
SELECT DATE_DIFF('2024-03-15', '2024-01-01', DAY);   -- 74
SELECT DATE_DIFF('2024-03-15', '2024-01-01', MONTH); -- 2

-- MySQL: DATEDIFF(end, start) returns days only
SELECT DATEDIFF('2024-03-15', '2024-01-01');  -- 74
-- For other units: TIMESTAMPDIFF(MONTH, start, end)
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-03-15');  -- 2

-- SQL Server: DATEDIFF(unit, start, end) — like Snowflake
SELECT DATEDIFF(day, '2024-01-01', '2024-03-15');  -- 74

-- INTERVAL is the cleanest way to add/subtract time periods.

-- Standard syntax (Postgres, Snowflake, BigQuery, MySQL all accept this)
SELECT CURRENT_DATE + INTERVAL '7 days';        -- 7 days from now
SELECT CURRENT_DATE - INTERVAL '1 month';       -- 1 month ago
SELECT CURRENT_TIMESTAMP + INTERVAL '3 hours';  -- 3 hours from now
SELECT CURRENT_TIMESTAMP - INTERVAL '15 minutes';

-- Filter the last N days
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Snowflake also accepts DATEADD for portability
SELECT DATEADD('day', 7, CURRENT_DATE);
SELECT DATEADD('month', -1, CURRENT_DATE);

-- Retention pattern: days from signup to first purchase
SELECT
  u.user_id,
  u.signup_date,
  MIN(o.order_date) AS first_order_date,
  MIN(o.order_date) - u.signup_date AS days_to_first_order
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.signup_date;

-- Current date/time at query execution time.
-- No parentheses required in standard SQL.

SELECT CURRENT_DATE;       -- 2024-03-15
SELECT CURRENT_TIMESTAMP;  -- 2024-03-15 14:30:00+00
SELECT NOW();              -- Postgres alias for CURRENT_TIMESTAMP

-- Engine-specific equivalents
-- Snowflake: CURRENT_DATE() / CURRENT_TIMESTAMP()
-- SQL Server: GETDATE() / SYSDATETIME()
-- BigQuery: CURRENT_DATE() / CURRENT_TIMESTAMP()
-- MySQL: CURDATE() / NOW()

-- Common patterns

-- Today's orders
SELECT * FROM orders WHERE order_date = CURRENT_DATE;

-- Last 24 hours
SELECT *
FROM orders
WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '24 hours';

-- Account age in days
SELECT
  user_id,
  signup_date,
  CURRENT_DATE - signup_date AS account_age_days
FROM users;

-- ETL load stamp
INSERT INTO load_log (table_name, loaded_at, row_count)
VALUES ('orders', CURRENT_TIMESTAMP, 1523);

-- WARNING: CURRENT_DATE is evaluated per-query, not per-row.
-- If you split a long ETL across queries, each gets a different
-- CURRENT_DATE if it spans midnight. Capture once at the top:
WITH run_meta AS (SELECT CURRENT_TIMESTAMP AS run_ts)
INSERT INTO log SELECT *, (SELECT run_ts FROM run_meta) FROM staging;

-- Timezones are one of the hardest problems in data engineering.
-- The rules below are battle-tested across many production pipelines.

-- PostgreSQL: AT TIME ZONE converts between zones
-- A bare timestamp interpreted as the specified zone -> converts to UTC
SELECT '2024-03-15 14:30:00'::timestamp AT TIME ZONE 'America/New_York';
-- 2024-03-15 18:30:00+00

-- A timestamptz displayed in the specified zone
SELECT '2024-03-15 18:30:00+00'::timestamptz AT TIME ZONE 'America/New_York';
-- 2024-03-15 14:30:00

-- Snowflake: CONVERT_TIMEZONE(from, to, timestamp)
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', created_at);

-- BigQuery: TIMESTAMP function with zone argument
SELECT TIMESTAMP(created_at, 'America/New_York');

-- MOST COMMON PATTERN: group by LOCAL day, not UTC day
SELECT
  DATE_TRUNC('day', created_at AT TIME ZONE 'America/New_York') AS local_day,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;
-- An order at 11pm Eastern = 4am UTC the next day. Without timezone
-- conversion, this order lands in the wrong day. Real example: a US
-- retailer's "daily sales" report was off by ~2% until someone added
-- AT TIME ZONE here. This is a real-world incident DataDriven seeded
-- one of its debrief problems from.

-- Three date patterns that appear in production dbt models and ETL.

-- Pattern 1: Cohort retention by signup month
SELECT
  DATE_TRUNC('month', u.signup_date) AS cohort_month,
  EXTRACT(MONTH FROM AGE(o.order_date, u.signup_date)) AS months_since_signup,
  COUNT(DISTINCT o.user_id) AS active_users
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

-- Pattern 2: 7-day rolling average revenue
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_revenue
ORDER BY date;

-- Pattern 3: Data freshness monitoring
SELECT
  table_name,
  MAX(loaded_at) AS last_load,
  EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(loaded_at))) / 3600
    AS hours_since_last_load,
  CASE
    WHEN MAX(loaded_at) < CURRENT_TIMESTAMP - INTERVAL '2 hours'
      THEN 'STALE'
    ELSE 'FRESH'
  END AS freshness_status
FROM load_log
GROUP BY table_name
ORDER BY hours_since_last_load DESC;