How to Convert Timezones in BigQuery

How do you convert UTC timestamps to local time zones in BigQuery?

Timezone conversion shifts TIMESTAMP or DATETIME values from one time zone to another or renders them in a desired zone for display.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why do I need timezone conversion in BigQuery?

Analytics often stores timestamps in UTC, but users expect local time.Converting on-the-fly keeps storage consistent while delivering location-aware reports.

Which BigQuery functions handle time zones?

Use FORMAT_TIMESTAMP for display, DATETIME(timestamp, timezone) to extract a local DATETIME, and TIMESTAMP(datetime, timezone) to re-cast that value into a new TIMESTAMP.

How do I convert UTC timestamps to a specific zone?

Wrap the TIMESTAMP in FORMAT_TIMESTAMP with the target IANA zone: FORMAT_TIMESTAMP('%F %T', order_date,'America/Los_Angeles').

How do I shift a timestamp from one zone to another?

1) Convert the source TIMESTAMP to a DATETIME in its original zone.
2) Re-cast that DATETIME to a TIMESTAMP in the target zone.

Example

TIMESTAMP(DATETIME(order_date,'UTC'),'America/New_York') AS ny_time

Can I keep the result as TIMESTAMP instead of text?

Yes.Use the two-step DATETIME → TIMESTAMP pattern. The resulting TIMESTAMP remains in UTC internally but reflects the target zone when formatted.

Best practices for reliable conversions?

• Store timestamps in UTC.
• Keep a user_timezone field for each customer.
• Always reference official IANA names (e.g., "Europe/Paris").
• Avoid hard-coding offsets; daylight-saving changes break them.

What are common mistakes?

See below.

Why How to Convert Timezones in BigQuery is important

How to Convert Timezones in BigQuery Example Usage


SELECT
    o.id,
    c.name,
    -- convert order_date (UTC) to each customer’s preferred zone
    TIMESTAMP(DATETIME(o.order_date,'UTC'), c.preferred_tz) AS order_time_local
FROM Orders o
JOIN Customers c ON c.id = o.customer_id;

How to Convert Timezones in BigQuery Syntax


-- Convert UTC TIMESTAMP to string in specific zone
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp_expr, 'America/Los_Angeles')

-- Convert UTC TIMESTAMP to TIMESTAMP in other zone (keeps TIMESTAMP type)
TIMESTAMP(                    -- recast back to TIMESTAMP
    DATETIME(                 -- interpret timestamp in source zone
        timestamp_expr,       -- e.g., Orders.order_date stored in UTC
        'UTC'                 -- source timezone
    ),
    'America/New_York'        -- target timezone
)

-- Example in ecommerce report
SELECT
    id,
    FORMAT_TIMESTAMP('%F %T', order_date,'America/Los_Angeles') AS local_order_time
FROM Orders;

Common Mistakes

Hard-coding numeric offsets like +02:00. Offsets change with daylight-saving rules. Fix: always use IANA zone names, e.g., 'Europe/Berlin'.
Formatting instead of converting when a TIMESTAMP is needed downstream. Fix: use DATETIME→TIMESTAMP pattern rather than FORMAT_TIMESTAMP if the result must stay TIMESTAMP.