Repository: tobymao/sqlglot

Issue:
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.

Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 2fa355e5..145a7d1c 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -302,6 +302,9 @@ class MySQL(Dialect):
 
         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 @@ class MySQL(Dialect):
             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/postgres.py b/sqlglot/dialects/postgres.py
index 710c6d8b..e739df7e 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -440,7 +440,7 @@ class Postgres(Dialect):
             self._match(TokenType.COMMA)
             value = self._parse_bitwise()
 
-            if part and part.is_string:
+            if part and isinstance(part, (exp.Column, exp.Literal)):
                 part = exp.var(part.name)
 
             return self.expression(exp.Extract, this=part, expression=value)
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index 5e1fa432..b39a9a50 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -17,6 +17,7 @@ from sqlglot.dialects.dialect import (
 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 Redshift(Postgres):
                 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 Redshift(Postgres):
         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 81db3190..58fdb620 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 Snowflake(Dialect):
             "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 Snowflake(Dialect):
         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 35944d52..410c12e0 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -132,6 +132,7 @@ class Spark(Spark2):
     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 6f0bcc2a..0608c2d9 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4845,6 +4845,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 04b805ee..d5aff88e 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -380,6 +380,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"
 
@@ -4083,3 +4086,20 @@ class Generator(metaclass=_Generator):
             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 = expression.args.get("timestamp")
+
+        if source_tz and timestamp:
+            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 d43a916e..19593691 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -128,6 +128,18 @@ 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)
+        )
+
+    return exp.ConvertTimezone.from_arg_list(args)
+
+
 class _Parser(type):
     def __new__(cls, clsname, bases, attrs):
         klass = super().__new__(cls, clsname, bases, attrs)
@@ -166,6 +178,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),
@@ -3198,6 +3211,15 @@ class Parser(metaclass=_Parser):
             self._match_set(self.JOIN_KINDS) and self._prev,
         )
 
+    def _parse_using_identifiers(self) -> t.List[exp.Expression]:
+        def _parse_column_as_identifier() -> t.Optional[exp.Expression]:
+            this = self._parse_column()
+            if isinstance(this, exp.Column):
+                return this.this
+            return this
+
+        return self._parse_wrapped_csv(_parse_column_as_identifier, optional=True)
+
     def _parse_join(
         self, skip_join_token: bool = False, parse_bracket: bool = False
     ) -> t.Optional[exp.Join]:
@@ -3238,7 +3260,7 @@ class Parser(metaclass=_Parser):
         if self._match(TokenType.ON):
             kwargs["on"] = self._parse_assignment()
         elif self._match(TokenType.USING):
-            kwargs["using"] = self._parse_wrapped_id_vars()
+            kwargs["using"] = self._parse_using_identifiers()
         elif not isinstance(kwargs["this"], exp.Unnest) and not (
             kind and kind.token_type == TokenType.CROSS
         ):
@@ -3248,7 +3270,7 @@ class Parser(metaclass=_Parser):
             if joins and self._match(TokenType.ON):
                 kwargs["on"] = self._parse_assignment()
             elif joins and self._match(TokenType.USING):
-                kwargs["using"] = self._parse_wrapped_id_vars()
+                kwargs["using"] = self._parse_using_identifiers()
             else:
                 joins = None
                 self._retreat(index)


Test patch:
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 9e672ec1..9117aa95 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -6,7 +6,6 @@ class TestRedshift(Validator):
     dialect = "redshift"
 
     def test_redshift(self):
-        self.validate_identity("1 div", "1 AS div")
         self.validate_all(
             "SELECT SPLIT_TO_ARRAY('12,345,6789')",
             write={
@@ -315,6 +314,7 @@ class TestRedshift(Validator):
         )
 
     def test_identity(self):
+        self.validate_identity("1 div", "1 AS div")
         self.validate_identity("LISTAGG(DISTINCT foo, ', ')")
         self.validate_identity("CREATE MATERIALIZED VIEW orders AUTO REFRESH YES AS SELECT 1")
         self.validate_identity("SELECT DATEADD(DAY, 1, 'today')")
@@ -337,6 +337,10 @@ class TestRedshift(Validator):
         self.validate_identity(
             """SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}', 'f4', 'f6', TRUE)"""
         )
+        self.validate_identity(
+            'DATE_PART(year, "somecol")',
+            'EXTRACT(year FROM "somecol")',
+        ).this.assert_is(exp.Var)
         self.validate_identity(
             "SELECT CONCAT('abc', 'def')",
             "SELECT 'abc' || 'def'",
@@ -433,6 +437,11 @@ ORDER BY
         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 16ef0196..0062aa4d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -138,6 +138,10 @@ WHERE
         self.validate_identity(
             "SELECT * FROM DATA AS DATA_L ASOF JOIN DATA AS DATA_R MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL) ON DATA_L.ID = DATA_R.ID"
         )
+        self.validate_identity(
+            "SELECT * FROM t1 INNER JOIN t2 USING (t1.col)",
+            "SELECT * FROM t1 INNER JOIN t2 USING (col)",
+        )
         self.validate_identity(
             "CURRENT_TIMESTAMP - INTERVAL '1 w' AND (1 = 1)",
             "CURRENT_TIMESTAMP() - INTERVAL '1 WEEK' AND (1 = 1)",
@@ -865,6 +869,28 @@ WHERE
             """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",