0Pricing
SQL Interview Prep · Lesson

Date Arithmetic and Intervals

Adding/subtracting periods and computing differences between dates.

Date Arithmetic and Intervals is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Date Math Shows Up in Interviews

Date arithmetic is one of the most practical SQL skills, so interviewers lean on it heavily for analyst and backend roles. Almost every business question has a time component: last 30 days, orders per week, days since signup.

The catch is that date functions are the least standardized part of SQL. The same operation has different syntax in PostgreSQL, MySQL, and SQL Server. A strong candidate states the idea clearly, then adapts the syntax to the dialect.

  • Adding or subtracting a period
  • Computing the difference between two dates
  • Working with INTERVAL values

The INTERVAL Type

In PostgreSQL and the SQL standard, a period of time is a first-class value called an interval. You add it to a date or timestamp with plain + and - operators.

This is the cleanest way to express "30 days ago" or "3 months from now" and interviewers love it because it reads like English.

SELECT
  CURRENT_DATE,
  CURRENT_DATE + INTERVAL '7 days'   AS next_week,
  CURRENT_DATE - INTERVAL '1 month'  AS last_month,
  NOW() + INTERVAL '90 minutes'      AS soon;

Adding Periods Across Dialects

Interviewers often ask: "how would you add 7 days in MySQL versus SQL Server?" Knowing the three big dialects signals real experience.

  • PostgreSQL: d + INTERVAL '7 days'
  • MySQL: DATE_ADD(d, INTERVAL 7 DAY)
  • SQL Server: DATEADD(day, 7, d)

The concept is identical; only the spelling changes. Always name the dialect you are writing for.

-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS due_date FROM orders;

-- SQL Server
SELECT DATEADD(day, 7, order_date) AS due_date FROM orders;

Difference Between Two Dates

The other half of date arithmetic is computing how far apart two dates are. The result depends on the unit and the dialect.

In PostgreSQL, subtracting two date values gives an integer number of days directly. Subtracting two timestamp values gives an interval instead.

-- Postgres: date - date returns an integer (days)
SELECT shipped_date - order_date AS days_to_ship
FROM orders;

DATEDIFF and Its Traps

DATEDIFF exists in MySQL and SQL Server but behaves differently, a favorite interview gotcha.

  • MySQL: DATEDIFF(end, start) returns whole days only.
  • SQL Server: DATEDIFF(unit, start, end) takes a unit and counts boundary crossings, not full units.

That boundary behavior matters: DATEDIFF(year, '2023-12-31', '2024-01-01') in SQL Server returns 1, even though only one day passed.

-- SQL Server: counts boundaries, not elapsed time
SELECT DATEDIFF(year, '2023-12-31', '2024-01-01'); -- 1
SELECT DATEDIFF(day,  '2023-12-31', '2024-01-01'); -- 1

Age in Years Done Right

"Compute a customer's age in years" is a classic. Naive day-difference divided by 365 drifts because of leap years. PostgreSQL has AGE() which returns a true calendar interval.

For a clean integer year count, extract the year part of the age.

-- Postgres
SELECT
  birth_date,
  AGE(CURRENT_DATE, birth_date)               AS exact_age,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age_years
FROM customers;

Worked Example: Orders in the Last 30 Days

A near-universal interview filter. The right way is to compare the date column against a computed cutoff, not to wrap the column in a function.

Comparing order_date to a constant cutoff keeps any index on order_date usable. We will return to this index point later.

SELECT COUNT(*) AS recent_orders
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

Half-Open Ranges for a Calendar Month

When asked for "all orders in March 2024", avoid BETWEEN '2024-03-01' AND '2024-03-31' on a timestamp column. That misses rows at 11pm on March 31 and is off-by-one prone.

The robust pattern is a half-open interval: >= the start and < the next month's start. It works for any column precision.

SELECT *
FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

EXTRACT and Date Parts

Pulling a single component out of a date is constant in reporting work. The standard function is EXTRACT(part FROM d), supported by PostgreSQL and MySQL.

  • EXTRACT(YEAR FROM d), MONTH, DAY
  • EXTRACT(DOW FROM d) for day-of-week
  • SQL Server uses DATEPART(weekday, d) instead

Note: extracting MONTH alone groups Marches from different years together, which is rarely what you want.

SELECT
  EXTRACT(YEAR  FROM order_ts) AS yr,
  EXTRACT(MONTH FROM order_ts) AS mo,
  COUNT(*) AS n
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

Deeper Example: Days Until Next Birthday

A tougher arithmetic puzzle that combines extraction and addition. Build this year's birthday from the birth month and day, and if it has already passed, roll to next year.

Walking through this aloud shows an interviewer you can reason about edge cases like a birthday that already occurred this year.

-- Postgres
SELECT
  name,
  CASE
    WHEN MAKE_DATE(EXTRACT(YEAR FROM CURRENT_DATE)::int,
                   EXTRACT(MONTH FROM birth_date)::int,
                   EXTRACT(DAY FROM birth_date)::int) >= CURRENT_DATE
    THEN MAKE_DATE(EXTRACT(YEAR FROM CURRENT_DATE)::int,
                   EXTRACT(MONTH FROM birth_date)::int,
                   EXTRACT(DAY FROM birth_date)::int) - CURRENT_DATE
    ELSE MAKE_DATE(EXTRACT(YEAR FROM CURRENT_DATE)::int + 1,
                   EXTRACT(MONTH FROM birth_date)::int,
                   EXTRACT(DAY FROM birth_date)::int) - CURRENT_DATE
  END AS days_until
FROM customers;

First and Last Day of the Month

"Give me the last day of each order's month" tests whether you reach for a built-in or reinvent it. PostgreSQL composes DATE_TRUNC with interval math; MySQL has LAST_DAY() directly.

The trick for last-day in Postgres: truncate to the month start, add one month, subtract one day.

-- Postgres
SELECT
  DATE_TRUNC('month', order_ts)                              AS month_start,
  DATE_TRUNC('month', order_ts) + INTERVAL '1 month - 1 day' AS month_end
FROM orders;

-- MySQL: LAST_DAY(order_ts)

Quick Check

Test your understanding of cross-dialect date difference behavior.

Recap: Date Arithmetic and Intervals

Key takeaways for the interview:

  • Use INTERVAL values with +/- in Postgres; DATE_ADD/DATEDIFF in MySQL; DATEADD/DATEDIFF in SQL Server.
  • SQL Server DATEDIFF counts boundary crossings, not elapsed units, the top gotcha.
  • For age in years, extract from AGE() rather than dividing days by 365.
  • Filter recent rows by comparing the column to a computed cutoff, and use half-open ranges (>= start AND < next) for month windows.

State the concept first, then adapt the dialect.

Frequently asked questions

Is the “Date Arithmetic and Intervals” lesson free?

Yes — the full text of “Date Arithmetic and Intervals” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Date Arithmetic and Intervals”?

Adding/subtracting periods and computing differences between dates. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Date Arithmetic and Intervals” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Date Arithmetic and Intervals
  2. Truncating and Bucketing Dates
  3. Parsing and Formatting Strings
  4. Time Zones and Timestamps
← Back to SQL Interview Prep