Is this the same as BETWEEN cutting off the last day?

No. BETWEEN on a timestamp drops the tail of the last day. EXTRACT(MONTH) keeps too much — every matching month across years. One under-counts, one over-counts.

What about EXTRACT(YEAR) AND EXTRACT(MONTH)?

That works for a calendar month if you also name the year. It's still clumsier than a range, and it still has the midnight-on-the-last-day issue if you then convert to dates.

Does DATE_TRUNC('month', created_at) = DATE_TRUNC('month', now()) fix it?

Yes for 'this calendar month,' if now() is in the timezone you mean. Pair it with the timezone habit from the metric-traps post. A range (>= first of month AND < first of next) is clearer to read.

Why does the model write EXTRACT?

The question said 'September' or 'this month.' EXTRACT maps the English word to a function. It does not add 'this year' unless you say so.

What about week of year?

Same trap. EXTRACT(WEEK) = 37 is every year-week 37. ISO weeks also disagree with US weeks. Use a date range for 'this week' too.

EXTRACT MONTH Without Year | SQL Date Filter

Q: Why does the model write EXTRACT?

The question said 'September' or 'this month.' EXTRACT maps the English word to a function. It does not add 'this year' unless you say so.

Q: What about week of year?

Same trap. EXTRACT(WEEK) = 37 is every year-week 37. ISO weeks also disagree with US weeks. Use a date range for 'this week' too.

You ask for "signups this month." The number is 4× what the admin dashboard shows. The SQL has no join, no OR leak, no missing HAVING. It used EXTRACT(MONTH FROM created_at) = 9. That is every September you have ever stored.

A month number is not a period

-- every September in the table
WHERE EXTRACT(MONTH FROM created_at) = 9

-- MySQL-shaped same bug
WHERE MONTH(created_at) = 9

September 2024 + 2025 + 2026 all pass. If you've been live two years, "this month" is three Septembers. The query is valid. The English is not.

-- this calendar month (half-open; includes the last day)
WHERE created_at >= DATE_TRUNC('month', NOW())
  AND created_at <  DATE_TRUNC('month', NOW()) + INTERVAL '1 month'

Name the timezone if NOW() is UTC and "this month" is Bangkok — that's the timezone trap, stacked on top. The extract bug happens even in one timezone.

This is the over-count cousin of BETWEEN missing the last day. That one is a short month. This one is too many years.

EXTRACT(WEEK) / WEEK() has the same shape: week 37 of every year, plus ISO vs US week numbering. Prefer a range for "this week" too.

How to catch it

Ask for MIN(created_at) and MAX(created_at) on the filtered rows. If the min is last year, the filter was a month number, not a period. Compare to a known dashboard number for this September only.

The prompt that heads this off

When I say this month / this week / September, use a timestamp range
(>= start AND < day after the period), not EXTRACT(MONTH) or MONTH().
Those match every year. Show MIN and MAX of the filtered dates.

The short version

MONTH = 9 means "any September." "This month" means one range. If the number looks like several years of that month stacked, read the WHERE before you read the join.

Read-only MCP gateway: mcpserver.design.

Related reading: