How to Convert Timezone in Snowflake

How do I convert timestamps between time zones in Snowflake?

CONVERT_TIMEZONE shifts a timestamp from one time zone to another in Snowflake.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why convert time zones in Snowflake?

Analytics often require aligning timestamps to a businessa0timea0zone. Using CONVERT_TIMEZONE lets you present dates in a usera0friendly zone without altering stored values.

What is the basic CONVERT_TIMEZONE syntax?

Use three arguments: source zone, target zone, and timestamp. When the sessiona0timea0zone is the source, supply only target zone and timestamp.

How do I convert order timestamps to UTC?

Wrap the order_date column in CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', order_date). The result is a TIMESTAMP_TZ in UTC.

Can I store converted values?

Yes. Use CREATE TABLE AS or UPDATE to persist the converted column, ensuring downstream queries always reference the correct zone.

What happens with daylight saving?

Snowflake applies IANA rules automatically. CONVERT_TIMEZONE adjusts for DST, so 2024-03-10 02:30 PST becomes 10:30 UTC.

Is casting required?

No. However, casting to TIMESTAMP_TZ can avoid ambiguity. Use order_date::timestamp_tz before conversion when data type is string.

How to audit conversions?

Add both local and UTC columns in SELECTs. Developers can verify values side-by-side during migration.

Why How to Convert Timezone in Snowflake is important

How to Convert Timezone in Snowflake Example Usage


-- Show each order time in both the customera0zone and UTC
SELECT 
    o.id,
    c.name,
    CONVERT_TIMEZONE('UTC', 'America/New_York', o.order_date)        AS order_date_est,
    CONVERT_TIMEZONE('UTC', 'Europe/London',     o.order_date)        AS order_date_gmt
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= DATEADD(day,-7,CURRENT_TIMESTAMP());

How to Convert Timezone in Snowflake Syntax


CONVERT_TIMEZONE(
    <source_tz> VARCHAR,
    <target_tz> VARCHAR,
    <timestamp> TIMESTAMP_LTZ | TIMESTAMP_NTZ | TIMESTAMP_TZ
)

-- Two-argument form (session time zone assumed as source)
CONVERT_TIMEZONE(
    <target_tz> VARCHAR,
    <timestamp> TIMESTAMP_LTZ | TIMESTAMP_NTZ | TIMESTAMP_TZ
)

-- Example with ecommerce data
SELECT 
    id,
    order_date,
    CONVERT_TIMEZONE('America/New_York','UTC', order_date) AS order_date_utc
FROM Orders;

Common Mistakes

Using wrong source time zone. If data is stored in UTC but you pass 'America/Los_Angeles' as the source, the result shifts twice. Always confirm the stored zone before calling CONVERT_TIMEZONE.
Forgetting string-to-timestamp casting. Passing a VARCHAR date directly causes implicit casts that can fail. Fix by using TO_TIMESTAMP or ::TIMESTAMP_TZ prior to conversion.