Which one is right, last month or the last 30 days?

Both are valid, for different jobs. 'Past 30 days' suits a trend line. 'Last month' suits anything that has to tie to finance, billing or a board report. The mistake is getting one when you asked for the other.

Does the same thing happen with 'last week'?

Yes. 'Now minus 7 days' is a rolling window. 'Last week' usually means a calendar week, and where a week starts depends on the database. DATE_TRUNC('week') in Postgres starts on Monday. In SQL Server it depends on a server setting.

What happens on the 31st?

Subtracting a month from March 31 lands on February 28 in Postgres, MySQL and SQL Server, because the day is clamped to the end of the shorter month. A rolling window that starts there is not exactly one month.

Why a half-open range instead of BETWEEN?

created_at >= start AND created_at < next_start includes the whole last day and nothing from the next month. BETWEEN ends at midnight of the last day and drops that day's activity.

Why did the AI not just ask which one I meant?

Models resolve vague words into the shortest expression that runs. now() minus a month is shorter than month-start arithmetic, so that is what you get unless you spell the boundaries out.

Last Month vs Last 30 Days SQL | Rolling Window

Q: Why did the AI not just ask which one I meant?

Models resolve vague words into the shortest expression that runs. now() minus a month is shorter than month-start arithmetic, so that is what you get unless you spell the boundaries out.

You ask for "revenue last month." The AI returns $84,210. Finance says September was $79,880. Nobody is wrong about the money. The query answered a different question.

-- what the AI wrote
SELECT SUM(amount) FROM orders
WHERE created_at >= now() - interval '1 month'

Say today is October 5th, mid-afternoon. That window starts September 5th, mid-afternoon, and runs to this minute. It drops September 1st to 4th and includes the first five days of October.

Rolling window versus calendar month

-- Postgres: the calendar month before this one
WHERE created_at >= DATE_TRUNC('month', now()) - interval '1 month'
  AND created_at <  DATE_TRUNC('month', now())

-- MySQL
WHERE created_at >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND created_at <  DATE_FORMAT(CURDATE(), '%Y-%m-01')

-- SQL Server
WHERE created_at >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
  AND created_at <  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

The end is < the start of the current month, not BETWEEN the last day. That keeps every row from the final day, which BETWEEN with dates drops.

The SQL runs either way. A rolling window gives a plausible number, so nothing looks broken until someone compares it with another report. Subtracting one month on March 31st also lands on February 28th in all three databases, so the "month" is not even a fixed length.

This is not the month extracted without the year, which mixes several Septembers together. Here the year is right and the window edges are wrong.

How to catch it

SELECT MIN(created_at), MAX(created_at) FROM orders WHERE <the filter the AI used>
-- for "last month" the first value should be the 1st at 00:00
-- and the last value should be before the 1st of this month

If the earliest row is not on the first of a month, the window is rolling.

The prompt that heads this off

When I say last month, last week or last quarter, use calendar boundaries:
start of the period inclusive, start of the next period exclusive.
Only use "the past N days" if I say "past N days". Show the exact start and
end timestamps you used.

The short version

now() - interval '1 month' means "since this time a month ago." "Last month" means the calendar month that just ended. If the AI's number is close to finance's but never equal, check where the window starts.

Read-only MCP gateway: mcpserver.design.

Related reading: