Repository: tobymao/sqlglot
Pull request: feat(duckdb): Transpile Snowflake's CONVERT_TIMEZONE 3-arg version
Fixes #3875

This PR attempts to transpile Snowflake's `CONVERT_TIMEZONE(source_tz, target_tz, timestamp_ntz)` 3-arg version to DuckDB.

Although there isn't a 1:1 mapping, the same result can be acquired by generating nested `TIMEZONE()` calls in DuckDB:

```SQL
TIMEZONE(target_tz, TIMEZONE(source_tz, timestamp_ntz::TIMESTAMP))
```

For instance:
```
Snowflake
--------------
SELECT CONVERT_TIMEZONE('AMERICA/LOS_ANGELES', 'AMERICA/NEW_YORK', '2024-08-06 09:10:00.000')
2024-08-06 12:10:00.000

DuckDB
------------
SELECT TIMEZONE('America/New_York', TIMEZONE('America/Los_Angeles', CAST('2024-08-06 09:10:00.000' AS TIMESTAMP)))
2024-08-06 12:10:00
```


Docs
--------
[Snowflake CONVERT_TIMEZONE](https://docs.snowflake.com/en/sql-reference/functions/convert_timezone) | [DuckDB TIMEZONE](https://duckdb.org/docs/sql/functions/timestamptz.html#timezonetext-timestamp)

Problem statement:
transpile from snowflake to duckdb with convert timezone is not working
**Fully reproducible code snippet**

The following snowflake query is transpile into a duckdb and fails
```
SELECT date_trunc('month', CONVERT_TIMEZONE('UTC', 'Europe/Berlin', anchor_hour)) AS ANCHOR,
                   SUM(sum_paid_usd) AS SUM_PAID,
                   SUM(CASE WHEN anchor_hour > current_timestamp() THEN sum_due_not_paid ELSE 0 END) AS SUM_UPCOMING,
                   SUM(CASE WHEN anchor_hour < current_timestamp() THEN sum_due_not_paid ELSE 0 END) AS SUM_OVERDUE,
                   SUM(count_paid_through_hb_usd) AS COUNT_PAID,
                   SUM(CASE WHEN anchor_hour > current_timestamp() THEN count_due_not_paid ELSE 0 END) AS COUNT_UPCOMING,
                   SUM(CASE WHEN anchor_hour < current_timestamp() THEN count_due_not_paid ELSE 0 END) AS COUNT_OVERDUE,
            FROM pdt_mongo_payment_reporting_aggregated
            WHERE company_id = '1111' AND anchor_hour BETWEEN '2023-12-31 23:00:00+00:00' AND '2024-07-31 21:59:59.999000+00:00'
            GROUP BY 1
            ORDER BY 1 ASC;
```

python code `transpile(raw, read="snowflake", write="duckdb", pretty=True)[0]`

Output query:
```
SELECT
  DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', 'Europe/Berlin', anchor_hour)) AS ANCHOR,
  SUM(sum_paid_usd) AS SUM_PAID,
  SUM(CASE WHEN anchor_hour > CURRENT_TIMESTAMP THEN sum_due_not_paid ELSE 0 END) AS SUM_UPCOMING,
  SUM(CASE WHEN anchor_hour < CURRENT_TIMESTAMP THEN sum_due_not_paid ELSE 0 END) AS SUM_OVERDUE,
  SUM(count_paid_through_hb_usd) AS COUNT_PAID,
  SUM(CASE WHEN anchor_hour > CURRENT_TIMESTAMP THEN count_due_not_paid ELSE 0 END) AS COUNT_UPCOMING,
  SUM(CASE WHEN anchor_hour < CURRENT_TIMESTAMP THEN count_due_not_paid ELSE 0 END) AS COUNT_OVERDUE
FROM pdt_mongo_payment_reporting_aggregated
WHERE
  company_id = '1111'
  AND anchor_hour BETWEEN '2023-12-31 23:00:00+00:00' AND '2024-07-31 21:59:59.999000+00:00'
GROUP BY
  1
ORDER BY
  1 ASC```

The `CONVERT_TIMEZONE` is not correct. fixing it to `timezone('Europe/Berlin', anchor_hour)` solves the issue. I believe that the issue is that we are converting from specific TZ to another TZ but I'm not sure.

Hints from issue discussion:
Hey @milonimrod,

Thanks for reporting this. From what I see, in Snowflake `CONVERT_TIMEZONE` has two versions depending on the parameter count, with the 3-arg version being `CONVERT_TIMEZONE(source_tz, target_tz, timestamp_ntz)`; This means that Snowflake can take a `TIMESTAMP` without timezone information and transform it to `target_tz` as if it was originally in `source_tz`.

DuckDB also has two versions of `TIMEZONE(text, timestamp / timestamp_tz)`, but the version that accepts a timestamp **without** timezone information will convert it to the `target_tz` as if it was originally in local time. Your solution works because you used `UTC`, but if the `source_tz` was different there'd be a difference in results afaict.

I've not been able to find a complete Snowflake -> DuckDB transpilation for it yet but hopefully it can be pieced together.

Hi @milonimrod,

I'm trying to understand how we can transform the 3-arg variant in Snowflake. DuckDB doesn't have a similar function where you can specify the source timezone, i.e. what timezone the input timestamp's in. It seems like in your example you omitted `'UTC'` altogether, but I'm not sure if this is correct. Is `anchor_hour` already a timestamp with UTC time zone, i.e. has type `TIMESTAMPTZ`? If not, DuckDB's [docs](https://duckdb.org/docs/sql/functions/timestamptz.html#timezonetext-timestamp) indicate that it will be interpreted in local time.

The 3-arg variant is not being transpiled intentionally because other dialects don't support this concept IIRC. I'm leaning towards closing this as not planned for now, but feel free to share ideas and I'll take another look later.
Hi, thank you for the quick response. Is there a way to create the mapping in duckDB by running the 3 args version as two nested calls?

The first will be to convert FROM tz1 to UTC and the second call from UTC TO tz2.
 Is this an option?
@milonimrod  Hmm, I think it can be done through nested `TIMEZONE()` calls without having to go through UTC actually:

`SELECT TIMEZONE(target_tz, TIMEZONE(source_tz, ts::TIMESTAMP))`

This should have the same results and it's also safe because Snowflake accepts only TIMESTAMP_NTZ in the 3-arg version afaict. I'll give it some more thought and will upload a PR later in the day if it's g2g.

It'd be awesome if you can also try this out on your column and see if it works as expected.



Hi @VaggelisD,
I can confirm that is is working as expected.
`SELECT TIMEZONE('America/Los_Angeles', TIMEZONE('Asia/Jerusalem', anchor_hour::TIMESTAMP)) AS pass, anchor_hour from read_parquet('/tmp/snowflake/output_qw2w2w_14/*.parquet', filename = true) limit 100;`

The output is as expected:
<img width="350" alt="image" src="https://github.com/user-attachments/assets/f7f1bcb0-6a57-4079-a4a3-54f6e47e3e2f">

Commit message: Remove unnecessary arg

Patch to review:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 2fa355e58f..145a7d1cb2 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -302,6 +302,9 @@ class Parser(parser.Parser):
 
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
+            "CONVERT_TZ": lambda args: exp.ConvertTimezone(
+                source_tz=seq_get(args, 1), target_tz=seq_get(args, 2), timestamp=seq_get(args, 0)
+            ),
             "DATE": lambda args: exp.TsOrDsToDate(this=seq_get(args, 0)),
             "DATE_ADD": build_date_delta_with_interval(exp.DateAdd),
             "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "mysql"),
@@ -1213,3 +1216,10 @@ def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
             dateadd = build_date_delta_with_interval(exp.DateAdd)([start_ts, interval])
 
             return self.sql(dateadd)
+
+        def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
+            from_tz = expression.args.get("source_tz")
+            to_tz = expression.args.get("target_tz")
+            dt = expression.args.get("timestamp")
+
+            return self.func("CONVERT_TZ", dt, from_tz, to_tz)
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index 5e1fa4322e..b39a9a5024 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -17,6 +17,7 @@
 from sqlglot.dialects.postgres import Postgres
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
+from sqlglot.parser import build_convert_timezone
 
 if t.TYPE_CHECKING:
     from sqlglot._typing import E
@@ -63,6 +64,7 @@ class Parser(Postgres.Parser):
                 unit=exp.var("month"),
                 return_type=exp.DataType.build("TIMESTAMP"),
             ),
+            "CONVERT_TIMEZONE": lambda args: build_convert_timezone(args, "UTC"),
             "DATEADD": _build_date_delta(exp.TsOrDsAdd),
             "DATE_ADD": _build_date_delta(exp.TsOrDsAdd),
             "DATEDIFF": _build_date_delta(exp.TsOrDsDiff),
@@ -155,6 +157,7 @@ class Generator(Postgres.Generator):
         HEX_FUNC = "TO_HEX"
         PARSE_JSON_NAME = "JSON_PARSE"
         ARRAY_CONCAT_IS_VAR_LEN = False
+        SUPPORTS_CONVERT_TIMEZONE = True
 
         # Redshift doesn't have `WITH` as part of their with_properties so we remove it
         WITH_PROPERTIES_PREFIX = " "
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 81db3190f7..58fdb62015 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -122,12 +122,6 @@ def _regexpilike_sql(self: Snowflake.Generator, expression: exp.RegexpILike) ->
     )
 
 
-def _build_convert_timezone(args: t.List) -> t.Union[exp.Anonymous, exp.AtTimeZone]:
-    if len(args) == 3:
-        return exp.Anonymous(this="CONVERT_TIMEZONE", expressions=args)
-    return exp.AtTimeZone(this=seq_get(args, 1), zone=seq_get(args, 0))
-
-
 def _build_regexp_replace(args: t.List) -> exp.RegexpReplace:
     regexp_replace = exp.RegexpReplace.from_arg_list(args)
 
@@ -268,7 +262,6 @@ class Parser(parser.Parser):
             "BITXOR": binary_from_function(exp.BitwiseXor),
             "BIT_XOR": binary_from_function(exp.BitwiseXor),
             "BOOLXOR": binary_from_function(exp.Xor),
-            "CONVERT_TIMEZONE": _build_convert_timezone,
             "DATE": _build_datetime("DATE", exp.DataType.Type.DATE),
             "DATE_TRUNC": _date_trunc_to_time,
             "DATEADD": _build_date_time_add(exp.DateAdd),
@@ -694,6 +687,7 @@ class Generator(generator.Generator):
         STAR_EXCEPT = "EXCLUDE"
         SUPPORTS_EXPLODING_PROJECTIONS = False
         ARRAY_CONCAT_IS_VAR_LEN = False
+        SUPPORTS_CONVERT_TIMEZONE = True
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 35944d52a8..410c12e010 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -132,6 +132,7 @@ def _parse_generated_as_identity(
     class Generator(Spark2.Generator):
         SUPPORTS_TO_NUMBER = True
         PAD_FILL_PATTERN_IS_REQUIRED = False
+        SUPPORTS_CONVERT_TIMEZONE = True
 
         TYPE_MAPPING = {
             **Spark2.Generator.TYPE_MAPPING,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 5250f37948..40036f50dd 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4840,6 +4840,10 @@ class Convert(Func):
     arg_types = {"this": True, "expression": True, "style": False}
 
 
+class ConvertTimezone(Func):
+    arg_types = {"source_tz": False, "target_tz": True, "timestamp": True}
+
+
 class GenerateSeries(Func):
     arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 3b61cb5755..ae69a291b9 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -378,6 +378,9 @@ class Generator(metaclass=_Generator):
     # Whether ARRAY_CONCAT can be generated with varlen args or if it should be reduced to 2-arg version
     ARRAY_CONCAT_IS_VAR_LEN = True
 
+    # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
+    SUPPORTS_CONVERT_TIMEZONE = False
+
     # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
     PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 
@@ -4079,3 +4082,20 @@ def arrayconcat_sql(self, expression: exp.ArrayConcat, name: str = "ARRAY_CONCAT
             rhs = self.expressions(expression)
 
         return self.func(name, expression.this, rhs)
+
+    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
+        if self.SUPPORTS_CONVERT_TIMEZONE:
+            return self.function_fallback_sql(expression)
+
+        source_tz = expression.args.get("source_tz")
+        target_tz = expression.args.get("target_tz")
+        timestamp = t.cast(exp.Expression, expression.args.get("timestamp"))
+
+        if source_tz:
+            timestamp = exp.AtTimeZone(
+                this=exp.cast(timestamp, exp.DataType.Type.TIMESTAMPNTZ), zone=source_tz
+            )
+
+        expr = exp.AtTimeZone(this=timestamp, zone=target_tz)
+
+        return self.sql(expr)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index cd02877435..e90617d03e 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -128,6 +128,20 @@ def build_array_constructor(
     return array_exp
 
 
+def build_convert_timezone(
+    args: t.List, default_source_tz: t.Optional[str] = None
+) -> t.Union[exp.ConvertTimezone, exp.Anonymous]:
+    if len(args) == 2:
+        source_tz = exp.Literal.string(default_source_tz) if default_source_tz else None
+        return exp.ConvertTimezone(
+            source_tz=source_tz, target_tz=seq_get(args, 0), timestamp=seq_get(args, 1)
+        )
+    if len(args) == 3:
+        return exp.ConvertTimezone.from_arg_list(args)
+
+    return exp.Anonymous.from_arg_list(args)
+
+
 class _Parser(type):
     def __new__(cls, clsname, bases, attrs):
         klass = super().__new__(cls, clsname, bases, attrs)
@@ -166,6 +180,7 @@ class Parser(metaclass=_Parser):
             safe=not dialect.STRICT_STRING_CONCAT,
             coalesce=dialect.CONCAT_COALESCE,
         ),
+        "CONVERT_TIMEZONE": build_convert_timezone,
         "DATE_TO_DATE_STR": lambda args: exp.Cast(
             this=seq_get(args, 0),
             to=exp.DataType(this=exp.DataType.Type.TEXT),
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 9e672ec1f1..22d8f624ab 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -433,6 +433,11 @@ def test_identity(self):
         self.validate_identity("SELECT ARRAY(1, 2, 3)")
         self.validate_identity("SELECT ARRAY[1, 2, 3]")
 
+        self.validate_identity(
+            """SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')""",
+            """SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-08-06 09:10:00.000')""",
+        )
+
     def test_values(self):
         # Test crazy-sized VALUES clause to UNION ALL conversion to ensure we don't get RecursionError
         values = [str(v) for v in range(0, 10000)]
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 16ef019699..78d01b20d9 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -865,6 +865,28 @@ def test_snowflake(self):
             """SELECT CAST(['foo'] AS VARIANT)[0]""",
         )
 
+        self.validate_all(
+            "SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')",
+            write={
+                "snowflake": "SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')",
+                "spark": "SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')",
+                "databricks": "SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')",
+                "redshift": "SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')",
+            },
+        )
+
+        self.validate_all(
+            "SELECT CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', '2024-08-06 09:10:00.000')",
+            write={
+                "snowflake": "SELECT CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', '2024-08-06 09:10:00.000')",
+                "spark": "SELECT CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', '2024-08-06 09:10:00.000')",
+                "databricks": "SELECT CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', '2024-08-06 09:10:00.000')",
+                "redshift": "SELECT CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', '2024-08-06 09:10:00.000')",
+                "mysql": "SELECT CONVERT_TZ('2024-08-06 09:10:00.000', 'America/Los_Angeles', 'America/New_York')",
+                "duckdb": "SELECT CAST('2024-08-06 09:10:00.000' AS TIMESTAMP) AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York'",
+            },
+        )
+
     def test_null_treatment(self):
         self.validate_all(
             r"SELECT FIRST_VALUE(TABLE1.COLUMN1) OVER (PARTITION BY RANDOM_COLUMN1, RANDOM_COLUMN2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MY_ALIAS FROM TABLE1",


Code review comments:
diff_hunk: @@ -4079,3 +4082,20 @@ def arrayconcat_sql(self, expression: exp.ArrayConcat, name: str = "ARRAY_CONCAT
             rhs = self.expressions(expression)
 
         return self.func(name, expression.this, rhs)
+
+    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
+        if self.SUPPORTS_CONVERT_TIMEZONE:
+            return self.function_fallback_sql(expression)
+
+        source_tz = expression.args.get("source_tz")
+        target_tz = expression.args.get("target_tz")
+        timestamp = t.cast(exp.Expression, expression.args.get("timestamp"))
path: sqlglot/generator.py
text: @user1:
Let's try this without the cast to take the partial parsing case into account.