LLMs, Time Zones and Currencies: Why Totals Differ

One order can land on two different days and be worth two different amounts, depending on which field you read. In the GA4 BigQuery export, Google says event_date follows the reporting time zone while event_timestamp is UTC, and raw purchase values are in local currency, not the reporting currency. A peer-reviewed study that examined 151 date and time bugs in open-source Python found time-zone mistakes to be the largest contributing factor. No study cited here measures time zone or currency errors in LLM-written SQL on store data.

What do the studies and documentation show?

Documentation sets the rules, and one peer-reviewed study shows which date and time mistakes are most common in real code. None measures LLM-written SQL on store data.

• Google for Developers (official guidance, 2023, page updated 2026) says "event_date is for the reporting time zone while event_timestamp is an UTC timestamp in microseconds", so a query on event_timestamp "has to be adjusted for the correct reporting time zone when comparing with UI numbers". Daily tables can be updated for up to 72 hours after the table date. Google Analytics Help gives the day rule: a visit that is Tuesday for the visitor and Monday in your time zone "is recorded as having occurred on Monday", and changing the time zone "only affects data going forward".
• Google Analytics Help, currency reference (official documentation) says "In BigQuery export, the raw transaction values included in the data are in local currency, and aren't based on the reporting currency." Reports convert "using the prior day's exchange rate", and a change of the property currency converts previous data too. Shopify's API keeps a shop currency and a presentment currency that "may differ from the shop's base currency when serving international customers or using multi-currency pricing".
• Tiwari et al. (peer-reviewed, MSR 2025, Distinguished Paper Award) qualitatively studied 151 bugs and their fixes in open-source Python projects on GitHub and report that "time-zone-related mistakes are the largest contributing factor to date/time bugs". It studies bugs in Python software, not SQL and not LLM output.

These are rules, not rates. No source cited here measures how often a store's time zone or currency differs from its GA4 property's.

Which clock and which currency does each system use?

1. GA4 reports. The property's reporting time zone sets the day boundary, and amounts show in the property currency.
2. The BigQuery export. event_date uses the reporting time zone and event_timestamp is UTC. ecommerce.purchase_revenue is in the transaction's local currency and ecommerce.purchase_revenue_in_usd is in USD.
3. Shopify. Each amount exists in the shop's base currency and in the presentment currency. Read the store's time zone in Shopify's settings and the property's in GA4 Admin, because they are separate settings.

For illustration: an order placed at 00:30 on 1 October in Amsterdam (UTC+2 that day) is 22:30 on 30 September in UTC, so a query that takes the UTC date files it under 30 September.

How do I check the clock and the currency on my own data?

Use a day at least 72 hours old, so late events have landed. First list the orders that change day with the clock. Replace the time zone with your property's.

SELECT ecommerce.transaction_id AS order_id,
       event_date AS ga4_date,
       FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp), 'UTC') AS utc_time,
       FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp), 'Europe/Amsterdam') AS local_time
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20260920' AND '20260927'
  AND DATE(TIMESTAMP_MICROS(event_timestamp)) != DATE(TIMESTAMP_MICROS(event_timestamp), 'Europe/Amsterdam')
ORDER BY event_timestamp
LIMIT 20;

Open those order IDs in the Shopify admin and read the date Shopify shows for each. Then split revenue by currency:

SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'currency') AS currency,
       COUNT(*)                               AS purchase_events,
       SUM(ecommerce.purchase_revenue)        AS revenue_local,
       SUM(ecommerce.purchase_revenue_in_usd) AS revenue_usd
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20260920' AND '20260927'
GROUP BY currency;

Read the output like this:

1. Clock, pass. Shopify's date for each listed order matches one column, local_time or utc_time, and every total you compare uses that clock on both sides.
2. Clock, fail. Shopify's date matches neither column, so the store and the property sit in different time zones, or a total mixes clocks, for example GA4's event_date against UTC dates from another system. Pick one zone and apply it to both sides.
3. Currency, pass. One currency row, or you compare only within a row. revenue_usd is the column for adding rows together.
4. Currency, fail. A total adds revenue_local across rows. The GA4 report converts at the prior day's rate while the export keeps the raw amount, so the two differ by design.

An LLM can write the timestamp and currency arithmetic quickly, but it doesn't know your property's time zone or your store's currency unless the prompt says so. Name both.

Sources, 30 September 2026: Bridge the gap between the Google Analytics UI and BigQuery export (Google for Developers, 2023); Set up Analytics for a website and/or app (Google Analytics Help, 2026); BigQuery Export and BigQuery Export schema (Google Analytics Help, 2026); Currency reference (Google Analytics Help, 2026); Shopify Admin API, Order and MoneyBag (Shopify, 2026); It's About Time: date and time bugs in open-source Python software (Tiwari et al., MSR, 2025).

Related answers

• Counting the same order twice in GA4
• How a join drops or repeats orders
• How to set custom attribution windows
• Attribution window
• Why Shopify and analytics numbers differ