Back to Blog

Why Your AI's Week Starts on Monday

Asked for this week's revenue on a Saturday and got a number that is short by Sunday. Postgres DATE_TRUNC('week') starts on Monday. A US week starts on Sunday, so Sunday's orders landed in last week.

You ask on Saturday, November 7, 2026: "What is this week's revenue?" The AI says $22,000. Your spreadsheet, which runs Sunday to Saturday, says $28,000. Sunday, November 1, had $6,000 of orders. They are in the AI's "last week."

SELECT SUM(amount)
FROM orders
WHERE created_at >= date_trunc('week', now())
  AND created_at <  date_trunc('week', now()) + interval '7 days'

In Postgres, date_trunc('week', ...) returns the Monday of that week. The week that contains Saturday, November 7, starts Monday, November 2. Sunday already happened, and it belongs to the previous bucket.

Three different things called "this week"

A rolling window is the last seven days, ending now. That is the same shape as "last month" meaning the last 30 days. A calendar week is a block of seven dates. Postgres, and the ISO standard, start that block on Monday. A US retail week starts it on Sunday. All three include a different Sunday.

The week number drifts even further. ISO week 1 is the week with the year's first Thursday, so the first and last days of a year change weeks:

SELECT EXTRACT(WEEK FROM DATE '2027-01-01') AS iso_week,
       EXTRACT(ISOYEAR FROM DATE '2027-01-01') AS iso_year
-- 53 and 2026. EXTRACT(YEAR) still says 2027.

January 1, 2027 is a Friday. It is ISO week 53 of 2026. Week 1 of 2027 starts Monday, January 4. A report of "week 1" that uses EXTRACT(WEEK) files New Year's Day under the previous year. The total for "2027" by week numbers will not match "2027" by date.

MySQL's default WEEK(created_at) starts on Sunday. WEEK(created_at, 3) is the ISO week. SQL Server follows DATEFIRST, which is Sunday on a US English server and Monday on many others. The AI writes the function the database taught it, which is not the calendar on your wall.

This is not UTC moving a few evening hours onto the next day. That one shifts the hours around midnight. This one moves a whole Sunday into the other week. Do the timezone conversion first, or the Sunday shift and the Monday shift stack.

How to fix it

-- Monday-start week, in a named zone, for a timestamptz column
SELECT date_trunc('week', created_at AT TIME ZONE 'America/New_York')::date AS week_start,
       SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1

For a Sunday start, shift one day, truncate to the Monday, and shift back:

SELECT (
         date_trunc('week', (created_at AT TIME ZONE 'America/New_York') + interval '1 day')
         - interval '1 day'
       )::date AS week_start_sunday,
       SUM(amount)
FROM orders
GROUP BY 1

Say which start you used, next to the number. "Week of Monday, November 2" and "week of Sunday, November 1" are both honest. "This week: $22,000" is not.

How to catch it

Ask for the range, not the label.

SELECT date_trunc('week', TIMESTAMPTZ '2026-11-07 America/New_York') AS week_start
-- 2026-11-02, a Monday. If you wanted Sunday Nov 1, this is the wrong block.

Then compare Sunday's revenue alone to the gap between the AI's week and yours. If the gap equals Sunday, the start day is the whole bug.

The prompt that heads this off

When I say "this week" or "last week," use weeks that start on Sunday
unless I say Monday. Show the first and last timestamp of the range.
Do not use a rolling 7 days. For week numbers, say whether they are ISO
(Monday, week 1 contains the first Thursday) and which year the week belongs to.

The short version

Postgres weeks start on Monday, and ISO week 1 can pull in late December or push January 1 into the previous year. Name the start day, show the range, and convert the timezone before you truncate.


Read-only MCP gateway: mcpserver.design.

Related reading: