DATE_ADD and DATE_DIFF Basics

Concepts covered: Date Math, Date Differences, Current Date, Existence Checks, Outer Joins

Most relative-time questions reduce to 2 moves. A shift takes a date and a span and returns another date: the trial end, the start of a window, the same day last year. A measure takes 2 dates and returns a number: days to first order, age of an open ticket, tenure. Requests ask "when" for the first and "how long" for the second, and naming which one you are answering keeps the result type straight, since a shift returns a date you can filter on and a measure returns an integer you can compare with a threshold.

Some engines spell the shift with the operator signup_date + INTERVAL '14 days' and others with a function such as DATE_ADD; this lesson writes the operator form. A negative span moves backwards, so on 2024-03-15 CURRENT_DATE - INTERVAL '30 days' is 2024-02-14. A day span moves by exact days. A month or year span moves along the calendar: 2024-03-01 minus 1 month is 2024-02-01, whatever the length of February. Pick the unit the request names. "30 days" is a day span and "last month" is a month span, and on 2024-03-15 they start the window on different dates.

-- Filters on events.event_date; today is 2024-03-15

-- Last 7 days including today: 2024-03-09 through 2024-03-15
WHERE event_date >= CURRENT_DATE - INTERVAL '6 days'

-- Last 7 complete days, today excluded: 2024-03-08 through 2024-03-14
WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
  AND event_date <  CURRENT_DATE

-- Last full calendar month: February 2024
WHERE event_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
  AND event_date <  DATE_TRUNC('month', CURRENT_DATE)

-- The same month a year ago: March 2023
WHERE event_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 year'
  AND event_date <  DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'

The first 2 patterns differ by 1 in the interval and by a whole day in the result. "Last 7 days" on a dashboard refreshed every morning usually means 7 complete days ending yesterday, because today is partial. On a live operations screen it usually means today plus the 6 days before. Both readings are common, and the mistake is choosing between them without saying so. "I'm counting 7 full days and excluding today" turns the off-by-one into a stated decision the interviewer can agree with or redirect.

Measuring the gap

DATE_DIFF('day', start_date, end_date) returns end minus start as a whole number of days, and in Postgres, end_date - start_date on 2 DATE values gives the same integer. Signup on 2024-01-10 and first order on 2024-01-17 is 7. An order on the signup day is 0. A negative result means the arguments are reversed or the data has an order before its signup, and either is worth a sentence to the interviewer. The query below measures days from signup to first order and flags customers who ordered within 7 days.

WITH first_orders AS (
  SELECT customer_id, MIN(CAST(ordered_at AS DATE)) AS first_order_date
  FROM orders
  GROUP BY customer_id
)
SELECT
  c.customer_id,
  c.signup_date,
  f.first_order_date,
  DATE_DIFF('day', c.signup_date, f.first_order_date) AS days_to_first_order,
  CASE WHEN DATE_DIFF('day', c.signup_date, f.first_order_date) <= 7
       THEN 1 ELSE 0 END AS activated_in_7_days
FROM customers c
LEFT JOIN first_orders f ON f.customer_id = c.customer_id;

Read the result row by row. C1 ordered on signup day, so the gap is 0. C2's gap of exactly 7 passes because the test is <= 7; with < 7 it would fail, and that single character is the "which day is day 0" question in code form. C4 never ordered: the LEFT JOIN keeps the customer, DATE_DIFF of a NULL date is NULL, the comparison is unknown, and the CASE falls through to 0. An inner join would have dropped C4 entirely and lifted the activation rate from 2 of 4 to 2 of 3.

Measure thresholds in days. DATE_DIFF('month', a, b) in Snowflake and BigQuery counts the month boundaries crossed between the 2 dates, and DuckDB does the same, so 2024-01-31 to 2024-02-01 is 1 month and 2024-02-01 to 2024-02-29 is 0. A filter like DATE_DIFF('month', signup_date, CURRENT_DATE) >= 1 therefore calls a customer who joined on 2024-02-29 a month old on 2024-03-01. "Customers older than 30 days" is DATE_DIFF('day', signup_date, CURRENT_DATE) > 30, which counts what it says.

A window for every row

The shift gets more useful when its starting date comes from the row. "Day-90 retention" asks, for each customer, whether they were active roughly 90 days after their own signup. There is no shared window: C1's sits at the end of March, C3's in mid-April. Adding the interval to signup_date inside the query computes a separate window per customer, and EXISTS checks for an event inside it.

-- Day-90 retention for customers who signed up in January 2024
SELECT
  c.customer_id,
  c.signup_date + INTERVAL '90 days' AS check_date,
  CASE WHEN EXISTS (
    SELECT 1 FROM events e
    WHERE e.customer_id = c.customer_id
      AND e.event_date >= c.signup_date + INTERVAL '89 days'
      AND e.event_date <  c.signup_date + INTERVAL '91 days'
  ) THEN 'retained' ELSE 'churned' END AS day_90_status
FROM customers c
WHERE c.signup_date >= '2024-01-01'
  AND c.signup_date <  '2024-02-01';

SELECT
  c.customer_id,
  c.signup_date + INTERVAL '90 days' AS check_date,
  CASE WHEN EXISTS (
    SELECT 1 FROM events e
    WHERE e.customer_id = c.customer_id
      AND e.event_date >= c.signup_date + INTERVAL '89 days'
      AND e.event_date <  c.signup_date + INTERVAL '91 days'
  ) THEN 'retained' ELSE 'churned' END AS day_90_status
FROM customers c

C1 signed up on 2024-01-01, so day 90 is 2024-03-31 and the window runs from 2024-03-30 up to, not including, 2024-04-01. Its event on 2024-03-31 falls inside: retained. C2 has 2 events. The one on 2024-01-12 is day 2 and sits far outside the window, while the one on 2024-04-09 is exactly day 90: retained. C3's window covers 2024-04-18 and 2024-04-19, and its only event, 2024-04-25, is day 96: churned. EXISTS answers yes or no per customer, so C2's 2 events produce 1 output row. Joining every event instead would have produced 2 rows for C2 and double-counted it in any retention rate built on top.

About this lesson

This section is part of the Date Arithmetic: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.