Repository: tobymao/sqlglot

Issue:
Issue with parsing of timestamp functions with time zones
I believe there may be an issue parsing and transpiling timestamp functions, especially when timezones are introduced. Here is an example of (what I think are) functionally equivalent statements in BQ, Snowflake, and DuckDB, assuming the variable start is a timestamp in UTC:

```{python}
bq_txt = 'TIMESTAMP(DATETIME(start, "America/New_York"))'
snow_txt = 'CONVERT_TIMEZONE("America/New_York",CAST(start as TIMESTAMP_TZ))'
duck_txt = 'CAST(start as TIMESTAMPTZ) AT TIME ZONE "America/New_York"'
```

Yet when you try to transpile these statements to the other languages, the correct changes are not made and the statements will fail to run in their new dialects. The command below:

```{python}
import sqlglot
print(sqlglot.transpile(bq_txt, read='bigquery', write='snowflake')[0])
print(sqlglot.transpile(bq_txt, read='bigquery', write='duckdb')[0])
print(sqlglot.transpile(snow_txt, read='snowflake', write='bigquery')[0])
print(sqlglot.transpile(snow_txt, read='snowflake', write='duckdb')[0])
print(sqlglot.transpile(duck_txt, read='duckdb', write='bigquery')[0])
print(sqlglot.transpile(duck_txt, read='duckdb', write='snowflake')[0])
```
Returns
```{text}
TIMESTAMP(DATETIME(start, 'America/New_York'))
TIMESTAMP(DATETIME(start, 'America/New_York'))
CONVERT_TIMEZONE(`America/New_York`, CAST(start AS TIMESTAMPTZ))
CONVERT_TIMEZONE("America/New_York", CAST(start AS TIMESTAMPTZ))
CAST(start AS TIMESTAMPTZ) AT TIME ZONE `America/New_York`
CAST(start AS TIMESTAMPTZ) AT TIME ZONE "America/New_York"
```


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index a3f9e6de..701377b7 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -221,6 +221,9 @@ class BigQuery(Dialect):
             **generator.Generator.TRANSFORMS,  # type: ignore
             **transforms.REMOVE_PRECISION_PARAMETERIZED_TYPES,  # type: ignore
             exp.ArraySize: rename_func("ARRAY_LENGTH"),
+            exp.AtTimeZone: lambda self, e: self.func(
+                "TIMESTAMP", self.func("DATETIME", e.this, e.args.get("zone"))
+            ),
             exp.DateAdd: _date_add_sql("DATE", "ADD"),
             exp.DateSub: _date_add_sql("DATE", "SUB"),
             exp.DatetimeAdd: _date_add_sql("DATETIME", "ADD"),
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index c50961cb..73fe20e9 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -179,6 +179,10 @@ class Snowflake(Dialect):
             "ARRAYAGG": exp.ArrayAgg.from_arg_list,
             "ARRAY_CONSTRUCT": exp.Array.from_arg_list,
             "ARRAY_TO_STRING": exp.ArrayJoin.from_arg_list,
+            "CONVERT_TIMEZONE": lambda args: exp.AtTimeZone(
+                this=seq_get(args, 1),
+                zone=seq_get(args, 0),
+            ),
             "DATE_TRUNC": date_trunc_to_time,
             "DATEADD": lambda args: exp.DateAdd(
                 this=seq_get(args, 2),
@@ -275,6 +279,9 @@ class Snowflake(Dialect):
             exp.Array: inline_array_sql,
             exp.ArrayConcat: rename_func("ARRAY_CAT"),
             exp.ArrayJoin: rename_func("ARRAY_TO_STRING"),
+            exp.AtTimeZone: lambda self, e: self.func(
+                "CONVERT_TIMEZONE", e.args.get("zone"), e.this
+            ),
             exp.DateAdd: lambda self, e: self.func("DATEADD", e.text("unit"), e.expression, e.this),
             exp.DateDiff: lambda self, e: self.func(
                 "DATEDIFF", e.text("unit"), e.expression, e.this


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a15e6b45..3da52df1 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -74,6 +74,17 @@ class TestDuckDB(Validator):
                 "spark": "TO_TIMESTAMP(x, 'M/d/yy h:mm a')",
             },
         )
+        self.validate_all(
+            "CAST(start AS TIMESTAMPTZ) AT TIME ZONE 'America/New_York'",
+            read={
+                "snowflake": "CONVERT_TIMEZONE('America/New_York', CAST(start AS TIMESTAMPTZ))",
+            },
+            write={
+                "bigquery": "TIMESTAMP(DATETIME(CAST(start AS TIMESTAMPTZ), 'America/New_York'))",
+                "duckdb": "CAST(start AS TIMESTAMPTZ) AT TIME ZONE 'America/New_York'",
+                "snowflake": "CONVERT_TIMEZONE('America/New_York', CAST(start AS TIMESTAMPTZ))",
+            },
+        )
 
     def test_sample(self):
         self.validate_all(