The days add up to the right month total. Is the split still wrong?

Yes. A timezone shift moves orders across midnight, so each day is off and the month is fine. A chart, a 'best day,' or a comparison to last Monday will be off even though the grand total matches finance.

Is this the bug where 'orders today' returns zero?

No. That one compares a timestamp to midnight and drops almost every row. This one returns a full day of rows. They are labeled with the database's date, which is not your date for the hours around midnight.

Why is the session UTC if the company is not?

Hosted Postgres (Supabase, Neon, Railway, RDS) usually leaves TimeZone at UTC. The AI's query never sets it, so ::date and CURRENT_DATE follow UTC.

What if the column is timestamp without time zone?

Then the value is a wall clock with no zone stored. Applying AT TIME ZONE as if it were timestamptz shifts it a second time, often by twice the offset. Check the column type before converting.

Does naming the zone in the question fix it?

Only if the SQL uses that zone. 'Monday in New York' in the chat, with created_at::date in the query, is still a UTC Monday. Ask the AI to show the expression it grouped by.

UTC Date Cast Shifts the Day | Timezone SQL

You ask: "What was Monday's revenue?" The AI says $18,400, from this:

SELECT SUM(amount)
FROM orders
WHERE (created_at)::date = DATE '2026-11-02'

One clean number. The rows are real. Monday in your office runs from midnight to midnight in New York. The database session is UTC, and timestamptz::date uses the session zone. An order at 8:30pm Monday in New York is 1:30am Tuesday in UTC, so it lands on Tuesday. Sunday night in New York, after 7pm in winter, lands on Monday.

The month still adds up, because every order lands on some day. The days are shifted at the edges. That is why a dashboard and the AI can disagree on Monday and agree on November.

A date cast is a timezone decision

created_at::date does not mean "the calendar day the customer saw." It means "the calendar day in SHOW timezone." On most hosted Postgres that is UTC.

SHOW timezone;  -- often UTC, whatever the company uses

This is not comparing a timestamp to midnight and getting zero rows. You get a normal total. It is not grouping by the raw timestamp and getting one row per second. You get one row per day. The label on that day is the wrong clock.

CURRENT_DATE and DATE_TRUNC('day', created_at) follow the same session zone. "Today" in the query starts at 00:00 UTC, which is yesterday evening in the US and morning in Asia.

How to fix it

Name the zone in the query. For a timestamptz column, convert to that zone, then take the date. The session zone no longer matters.

-- Monday in New York, half-open so the next midnight is not included
SELECT SUM(amount)
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-11-02 00:00:00 America/New_York'
  AND created_at <  TIMESTAMPTZ '2026-11-03 00:00:00 America/New_York'

-- a daily series in that zone
SELECT (created_at AT TIME ZONE 'America/New_York')::date AS local_day,
       SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1

In MySQL, with the column stored as UTC:

SELECT DATE(CONVERT_TZ(created_at, '+00:00', 'America/New_York')) AS local_day,
       SUM(amount)
FROM orders
GROUP BY local_day

If the column is timestamp without time zone, find out which zone those numbers were written in before converting. Converting twice shifts every row by hours and the totals still look fine.

How to catch it

SELECT current_setting('TimeZone') AS session_zone

If that is UTC and the business is not, any ::date, DATE_TRUNC('day', ...) or CURRENT_DATE in the AI's query is a UTC boundary. Re-run Monday with the zoned range above. A different total means the first number included the wrong evening.

The prompt that heads this off

When I name a day, week, or "today," use this timezone: America/New_York.
For timestamptz columns, convert with AT TIME ZONE before taking the date.
Do not use ::date or CURRENT_DATE alone. Show me the expression you grouped
or filtered by, including the zone.

The short version

::date uses the database clock, and that clock is usually UTC. Name the zone and convert before you take the day. A month that adds up does not mean Monday is Monday.

Read-only MCP gateway: mcpserver.design.

Related reading: