Redshift CONVERT_TIMEZONE in Snowflake, Databricks and BigQuery
Snowflake and Databricks both have CONVERT_TIMEZONE, but the two-argument form means something different in each: Redshift assumes the source is UTC, Databricks uses the session time zone, and Snowflake expects a time-zone-aware input. Pass the source time zone explicitly to stay safe.
Redshift function: CONVERT_TIMEZONE(['source_timezone',] 'target_timezone', 'timestamp') · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | CONVERT_TIMEZONE(source_tz, target_tz, source_timestamp_ntz) | The three-argument form returns TIMESTAMP_NTZ. The two-argument form takes a TIMESTAMP_TZ or LTZ value and returns TIMESTAMP_TZ. | Snowflake docs |
| Databricks | Same name | convert_timezone([sourceTz, ]targetTz, sourceTs) | Databricks SQL and Databricks Runtime 13.3 LTS and above. Without sourceTz the session time zone is the source. | Databricks docs |
| BigQuery | Rewrite | DATETIME(TIMESTAMP(ts, 'source_tz'), 'target_tz') | TIMESTAMP(datetime, tz) interprets a DATETIME in a zone; DATETIME(timestamp, tz) renders it in another. | BigQuery docs |
Behavior differences to check
- CONVERT_TIMEZONE('Asia/Shanghai', ts) in Redshift converts from UTC. Rewrite it as the three-argument CONVERT_TIMEZONE('UTC', 'Asia/Shanghai', ts) before migrating; it means the same thing in Redshift, Snowflake and Databricks.
Examples
UTC event time in Shanghai local time
SELECT CONVERT_TIMEZONE('UTC', 'Asia/Shanghai', event_time) AS local_time FROM events;SELECT CONVERT_TIMEZONE('UTC', 'Asia/Shanghai', event_time) AS local_time FROM events;SELECT convert_timezone('UTC', 'Asia/Shanghai', event_time) AS local_time FROM events;SELECT DATETIME(TIMESTAMP(event_time, 'UTC'), 'Asia/Shanghai') AS local_time FROM events;