How to Convert Time Zones in Redshift

What does CONVERT_TIMEZONE do in Redshift?

CONVERT_TIMEZONE translates a TIMESTAMP (optionally with time zone) from a source zone to a target zone, returning a TIMESTAMPTZ that represents the identical instant in the new zone.

What is the exact syntax?

Use one, two, or three arguments: CONVERT_TIMEZONE(target_zone, timestamp), or CONVERT_TIMEZONE(source_zone, target_zone, timestamp). Zones accept IANA names (e.g., 'America/New_York') or offsets (e.g., '+02').

How do I convert UTC to the customer’s local zone?

Pass 'UTC' as the source zone and the customer’s zone as the target. Store customer zones in a Customers.timezone column to automate conversions.

Code example: shifting order timestamps

SELECT o.id,
CONVERT_TIMEZONE('UTC', c.timezone, o.order_date) AS local_order_time
FROM Orders o
JOIN Customers c ON c.id = o.customer_id;

Can I rely on session time zone instead?

Redshift supports SET TIME ZONE, but explicit CONVERT_TIMEZONE calls keep queries deterministic and safer for multi-user clusters.

Best practices for time-zone handling

Store all timestamps in UTC, keep user time zones in a lookup column, always cast VARCHAR zones to TEXT, and document daylight-saving assumptions.

Common mistakes and how to avoid them

Argument order mix-ups: Always provide source_zone before target_zone. Reverse order yields wrong offsets.

Using TIMESTAMP without time zone: If the column lacks zone, Redshift assumes the session zone. Cast to TIMESTAMPTZ or specify the source zone explicitly.

Need multiple daylight-saving safe conversions?

Use IANA zone names instead of numeric offsets to let Redshift apply historical DST rules automatically.

What happens if the zone string is NULL?

CONVERT_TIMEZONE returns NULL. Coalesce zone values or fall back to a default like 'UTC'.