GA4 BigQuery Timezone Handling

GA4’s BigQuery export contains three different timezone references, and they do not agree with each other. Queries that look correct will silently include or exclude events at day boundaries, producing results that differ from the GA4 interface without any obvious explanation.

The Three Timezone Contexts

Each field is internally consistent. The problem arises when you mix them in a single query.

event_timestamp: Always UTC

The timestamp field records when the event occurred in Coordinated Universal Time, stored as microseconds since the Unix epoch. To convert to a human-readable time, use BigQuery’s timestamp and datetime functions:

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_utc_timestamp,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Europe/Paris') AS event_paris_time,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'America/New_York') AS event_ny_time
FROM `project.analytics_123456789.events_*`

If you’re building a sessionized events table, decide on one canonical timezone for your team and convert at the base layer. Centralizing this decision prevents analysts from each applying their own conversion and getting different results.

event_date: Property Timezone

The event_date field uses your GA4 property’s configured timezone, stored as a YYYYMMDD string (e.g., 20260127). If your property is configured for Europe/Paris and an event occurs at 23:30 UTC on January 26th, that event gets event_date = '20260127' — because it’s already January 27th in Paris.

This is actually useful: event_date aligns with what your business considers “today,” making it the natural partition field for analysis. But it creates a subtlety: event_date and the UTC date of event_timestamp can differ by one day for events near midnight.

_TABLE_SUFFIX: Pacific Time

The events_* wildcard table notation relies on _TABLE_SUFFIX to filter date-sharded tables, but the suffix follows Pacific Time (Google’s corporate timezone), not UTC and not your property timezone.

The practical consequence: if your property timezone is something like Europe/Berlin (UTC+1), an event occurring at midnight January 27th Berlin time actually occurred at 23:00 UTC on January 26th — which falls on January 26th in Pacific Time. That event’s event_date is 20260127 but its table is events_20260126.

-- This query may miss events you expect to see
-- for a European property with December 31st events:
WHERE _TABLE_SUFFIX = '20261231'
  AND event_date = '20261231'
-- Some January 1st events (in property timezone) live in
-- the December 31st table (in Pacific Time)

Practical Query Approaches

Using _TABLE_SUFFIX for Range Filtering

Always filter _TABLE_SUFFIX to avoid scanning your entire dataset. For correctness at date boundaries, expand your suffix range by one day in each direction:

SELECT
  event_name,
  COUNT(*) AS events
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  -- Then filter on event_date for the actual date range you want:
  AND event_date BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY event_name

The _TABLE_SUFFIX filter reduces scan cost. The event_date filter ensures correctness. Using both together is the robust pattern.

Filtering by Time of Day

When you need to filter events to a specific local time window, convert event_timestamp explicitly:

SELECT
  event_name,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'America/Chicago') AS local_time
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20260127'
  AND DATETIME(TIMESTAMP_MICROS(event_timestamp), 'America/Chicago')
      BETWEEN '2026-01-27 09:00:00' AND '2026-01-27 17:00:00'

Standardizing in Your Base Model

In a dbt base model, standardize all time representations at the source:

SELECT
  event_date,
  TIMESTAMP_MICROS(event_timestamp) AS event_timestamp_utc,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), '{{ var("property_timezone") }}') AS event_datetime_local,
  DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), '{{ var("property_timezone") }}')) AS event_date_local,
  -- Keep original for reference
  event_timestamp AS event_timestamp_micros
FROM {{ source('ga4', 'events') }}
WHERE _TABLE_SUFFIX BETWEEN ...

Storing the converted values in the base model means every downstream query gets consistent, pre-converted timestamps without repeating the TIMESTAMP_MICROS and DATETIME logic.

The Common Mistake Pattern

The scenario that burns most people: they’re filtering for “last month’s data” and their counts don’t match the GA4 interface. The mismatch is often a day-boundary issue — _TABLE_SUFFIX filtered too tightly, cutting off events from the first or last day because those events sat in an adjacent table due to timezone differences.

If you’re working with a property in UTC, the timezone problem largely disappears: all three references agree (within Pacific Time’s few-hour offset from UTC). Properties in UTC+8 through UTC+14 are most exposed because their “next day” in local time is still “current day” in Pacific Time for many hours.

The fix is mechanical: always use event_date for business logic date filtering, use _TABLE_SUFFIX only for cost control with a slightly wider window than your actual date range, and document your property timezone in the dbt project variables so the base model handles conversion correctly.