How does the last n days query work?

CURRENT_DATE - INTERVAL '30 days' is the start of the window; the filter is inclusive of that day. order_date = and < rather than BETWEEN avoids the end-of-day timestamp problem.

What commonly goes wrong with last n days in SQL?

BETWEEN with timestamps excludes most of the last day because it ends at midnight; use a half-open range.

Last N Days in SQL

Q: What is last n days in SQL?

A last-N-days filter keeps only rows from the most recent window, such as the previous 30 complete days.

Example query

This example assumes: orders(id, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT COUNT(*) AS orders,
       SUM(total_amount) AS sales
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND order_date <  CURRENT_DATE;

How it works

CURRENT_DATE - INTERVAL '30 days' is the start of the window; the filter is inclusive of that day.
order_date < CURRENT_DATE excludes today, which is incomplete, so you get the last 30 complete days.
Using >= and < rather than BETWEEN avoids the end-of-day timestamp problem.

MySQL, BigQuery, SQL Server and SQLite differences

MySQL: order_date >= CURDATE() - INTERVAL 30 DAY. SQL Server: order_date >= DATEADD(day, -30, CAST(GETDATE() AS date)).
BigQuery: order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY). SQLite: order_date >= date('now', '-30 days').

Common mistakes

BETWEEN with timestamps excludes most of the last day because it ends at midnight; use a half-open range.
'Last 30 days' includes today in some tools and excludes it in others; state which one you use.
CURRENT_DATE uses the server's time zone, which may differ from your business day.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

How much did we sell in the last 30 complete days?
How many signups did we get in the past 7 days?