Session Time Zone Drift in a TIMESTAMP WITH TIME ZONE Report

Different instants, identical text. DATE '2024-06-01' carries no time zone. When Oracle compares a DATE with a TIMESTAMP WITH TIME ZONE value, it converts the DATE to TIMESTAMP WITH TIME ZONE using the session time zone.

• New York session: 2024-06-01 00:00 America/New_York = 2024-06-01 04:00 UTC.
• London session: 2024-06-01 00:00 Europe/London = 2024-05-31 23:00 UTC.

The London window starts four hours earlier in absolute time (and its July boundary shifts as well because BST is in effect), so it captures orders placed late on 31 May UTC that New York excludes. 2,564 rows is plausible for four hours of a global order feed. Both sessions run the same SQL and each is correct relative to its own local midnight, which is exactly why the report cannot be reconciled.

The string variant. '01-JUN-24' is a string. Compared with a TIMESTAMP WITH TIME ZONE column, Oracle converts it using NLS_TIMESTAMP_TZ_FORMAT, not NLS_DATE_FORMAT. In a session where that parameter is the default that includes fractional seconds and a TZR element, parsing 01-JUN-24 raises ORA-01861. In a session where the parameter happens to match, the literal parses, the session zone is attached, and a third row set is returned silently. A loud failure is the better outcome; neither is acceptable.

Fix the window to an instant and keep the column bare.

SELECT COUNT(*)
FROM   orders
WHERE  order_ts >= TIMESTAMP '2024-06-01 00:00:00 Europe/London'
AND    order_ts <  TIMESTAMP '2024-07-01 00:00:00 Europe/London';

Both sessions now compare the same two instants; the session time zone is irrelevant. Using UTC instead works equally well if finance agrees the reporting month is a UTC month - but that is a business decision, so document it.

Index implications.

• The predicate is a range on the indexed column itself, so the optimizer can use the B-tree on order_ts for an index range scan no matter what the session time zone is.
• TRUNC(order_ts) wraps the column in a function. Without a matching function-based index the plan degrades to a full scan of 4.2 million rows. Worse, TRUNC on a time-zone-aware value is evaluated in the session zone, so the day boundaries move with the session: you would have traded a correctness bug for a performance bug plus the same correctness bug.
• Prefer bind variables typed TIMESTAMP WITH TIME ZONE over literals baked into application code, so plans stay sharable and windows remain explicit.

Avoid SYSDATE-based local truncation and NLS-dependent date strings in any report that must reconcile across regions.

Convert to the reporting region, then truncate.

SELECT TRUNC(order_ts AT TIME ZONE 'Europe/London') AS order_day,
       COUNT(*) AS orders
FROM   orders
WHERE  order_ts >= TIMESTAMP '2024-06-01 00:00:00 Europe/London'
AND    order_ts <  TIMESTAMP '2024-07-01 00:00:00 Europe/London'
GROUP BY TRUNC(order_ts AT TIME ZONE 'Europe/London')
ORDER BY order_day;

Reasoning.

• AT TIME ZONE on a TIMESTAMP WITH TIME ZONE returns a plain TIMESTAMP in that region, with no session dependency. Truncating that value gives local midnight.
• The GROUP BY expression must repeat the SELECT expression; Oracle does not accept a select-list alias by name in GROUP BY (positional GROUP BY 1 also works).
• The WHERE clause is still a range on the raw column, so the index remains usable; only the grouping expression is computed per row.
• For a genuinely multi-region report, parameterise the region and run one query per region, or derive the region from customer data with a CASE expression.
• Do not CAST(order_ts AS DATE): that pins the day boundary to the session zone and reintroduces the original drift.