Dates & time
SQL · 5 interview questions
Date handling is where SQL dialects diverge most, so the honest interview answer usually names the dialect. MySQL has DATEDIFF(a, b) returning whole days and DATE_ADD(d, INTERVAL 1 DAY); Postgres subtracts dates directly and uses d + INTERVAL '1 day'. Saying "in MySQL…" signals you know there's a difference rather than that you only know one system.
The recurring bug is the boundary between a date and a timestamp. A DATE literal is midnight, so BETWEEN '2024-01-01' AND '2024-01-31' against a timestamp column silently excludes almost the entire 31st — everything after 00:00:00. The robust form is a half-open range: >= '2024-01-01' AND < '2024-02-01', which needs no thought about month lengths or leap years.
Grouping by month means truncating to month first, usually with DATE_FORMAT(d, '%Y-%m') in MySQL or DATE_TRUNC('month', d) in Postgres. Grouping by the raw date gives you one row per day, which is a very common and very confusing wrong answer.
One performance note worth having ready: wrapping the column in a function — WHERE YEAR(created_at) = 2024 — usually prevents the database from using an index on that column. Rewriting it as a range over the raw column keeps the index usable.
Dates & time interview questions
- How do you find rows exactly one day apart, in MySQL?
DATEDIFF(a.date, b.date) = 1, which returns whole days and gets the sign from the argument order. Postgres would bea.date - b.date = 1. Don't do it with id arithmetic — ids aren't guaranteed to track dates.- Why they ask: Naming the dialect is half the point of the answer.
- Why does
BETWEEN '2024-01-01' AND '2024-01-31'miss most of the 31st? - The literal becomes 2024-01-31 00:00:00, so anything later that day fails the comparison. Use a half-open range instead:
>= '2024-01-01' AND < '2024-02-01'. It also sidesteps month lengths and leap years entirely. - Why they ask: A real reporting bug that quietly under-counts one day per period.
- How do you group rows by month?
- Truncate first:
DATE_FORMAT(d, '%Y-%m')in MySQL,DATE_TRUNC('month', d)in Postgres. Include the year — grouping by month number alone merges January 2023 with January 2024. - Why they ask: The forgotten-year mistake is extremely common and produces plausible-looking totals.
- Why is
WHERE YEAR(created_at) = 2024a performance problem? - Wrapping the column in a function usually makes an index on it unusable, forcing a full scan. Rewrite as a range on the bare column —
>= '2024-01-01' AND < '2025-01-01'— which an index can serve. - Why they ask: A rare chance to show query-optimisation awareness in a question that looks like syntax.
- How do you express 'in the last 30 days including today'?
date > DATE_SUB(end_date, INTERVAL 30 DAY) AND date <= end_date. The strict>with 30 gives exactly 30 days inclusive of the endpoint;>=gives 31. Decide which the question wants and say which you chose.- Why they ask: Off-by-one on rolling windows is the most common failure on these problems.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.