Repository: tobymao/sqlglot

Issue:
Snowflake's `TO_TIME` not Transpiled
Hi! I'm trying to transpile a Snowflake Query into DuckDB query but the [`TO_TIME` function](https://docs.snowflake.com/en/sql-reference/functions/to_time) is not being transpiled. I saw that it's supported [here](https://github.com/tobymao/sqlglot/blob/8b465d498e0aa9feee53306f631e258443ee3060/sqlglot/dialects/snowflake.py#L464) but it's not behaving as expected.

**CODE**
```
QUERY = """
SELECT     
    to_date(convert_timezone('UTC', 'US/Pacific', events.createdat)) as pst_date,
    to_time(convert_timezone('UTC', 'US/Pacific', events.createdat)) as pst_time
FROM my_db.schema.events 
"""
duck_db_query= sqlglot.transpile(QUERY, read="snowflake",write="duckdb")
```

**RAISES**
Raised when trying to query DuckDB
```
duckdb.duckdb.CatalogException: Catalog Error: Scalar Function with name to_time does not exist!
Did you mean "to_timestamp"?
LINE 1: ...ONE 'US/Pacific' AS DATE) AS pst_date, TO_TIME(CAST(events.createdat A...
```

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index ac69994470..0601a36781 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -67,8 +67,9 @@ def _builder(args: t.List) -> exp.Func:
                     expr.set("safe", safe)
                     return expr
 
-        if kind == exp.DataType.Type.DATE and not int_value:
-            formatted_exp = build_formatted_time(exp.TsOrDsToDate, "snowflake")(args)
+        if kind in (exp.DataType.Type.DATE, exp.DataType.Type.TIME) and not int_value:
+            klass = exp.TsOrDsToDate if kind == exp.DataType.Type.DATE else exp.TsOrDsToTime
+            formatted_exp = build_formatted_time(klass, "snowflake")(args)
             formatted_exp.set("safe", safe)
             return formatted_exp
 
@@ -449,6 +450,7 @@ class Parser(parser.Parser):
             "TIMESTAMP_NTZ_FROM_PARTS": build_timestamp_from_parts,
             "TRY_PARSE_JSON": lambda args: exp.ParseJSON(this=seq_get(args, 0), safe=True),
             "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True),
+            "TRY_TO_TIME": _build_datetime("TRY_TO_TIME", exp.DataType.Type.TIME, safe=True),
             "TRY_TO_TIMESTAMP": _build_datetime(
                 "TRY_TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP, safe=True
             ),
@@ -979,6 +981,9 @@ class Generator(generator.Generator):
             exp.TsOrDsToDate: lambda self, e: self.func(
                 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e)
             ),
+            exp.TsOrDsToTime: lambda self, e: self.func(
+                "TRY_TO_TIME" if e.args.get("safe") else "TO_TIME", e.this, self.format_time(e)
+            ),
             exp.UnixToTime: rename_func("TO_TIMESTAMP"),
             exp.Uuid: rename_func("UUID_STRING"),
             exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index f52f41478e..ed4b64421e 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -6654,7 +6654,7 @@ class TsOrDsToDatetime(Func):
 
 
 class TsOrDsToTime(Func):
-    pass
+    arg_types = {"this": True, "format": False, "safe": False}
 
 
 class TsOrDsToTimestamp(Func):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 3ceee2158a..bdda2ba8f7 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3934,6 +3934,16 @@ def toarray_sql(self, expression: exp.ToArray) -> str:
 
     def tsordstotime_sql(self, expression: exp.TsOrDsToTime) -> str:
         this = expression.this
+        time_format = self.format_time(expression)
+
+        if time_format:
+            return self.sql(
+                exp.cast(
+                    exp.StrToTime(this=this, format=expression.args["format"]),
+                    exp.DataType.Type.TIME,
+                )
+            )
+
         if isinstance(this, exp.TsOrDsToTime) or this.is_type(exp.DataType.Type.TIME):
             return self.sql(this)
 


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e1f43882cf..cb38af753d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -36,7 +36,6 @@ def test_snowflake(self):
         self.validate_identity("SELECT CAST(obj AS OBJECT(x CHAR) RENAME FIELDS)")
         self.validate_identity("SELECT CAST(obj AS OBJECT(x CHAR, y VARCHAR) ADD FIELDS)")
         self.validate_identity("SELECT TO_TIMESTAMP(123.4)").selects[0].assert_is(exp.Anonymous)
-        self.validate_identity("SELECT TO_TIME(x) FROM t")
         self.validate_identity("SELECT TO_TIMESTAMP(x) FROM t")
         self.validate_identity("SELECT TO_TIMESTAMP_NTZ(x) FROM t")
         self.validate_identity("SELECT TO_TIMESTAMP_LTZ(x) FROM t")
@@ -730,13 +729,6 @@ def test_snowflake(self):
                 "spark": "SELECT CAST('2013-04-05 01:02:03' AS TIMESTAMP)",
             },
         )
-        self.validate_all(
-            "SELECT TO_TIME('12:05:00')",
-            write={
-                "bigquery": "SELECT CAST('12:05:00' AS TIME)",
-                "snowflake": "SELECT CAST('12:05:00' AS TIME)",
-            },
-        )
         self.validate_all(
             "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/DD/yyyy hh24:mi:ss')",
             read={
@@ -1296,6 +1288,37 @@ def test_timestamps(self):
             },
         )
 
+        self.validate_identity("SELECT TO_TIME(x) FROM t")
+        self.validate_all(
+            "SELECT TO_TIME('12:05:00')",
+            write={
+                "bigquery": "SELECT CAST('12:05:00' AS TIME)",
+                "snowflake": "SELECT CAST('12:05:00' AS TIME)",
+                "duckdb": "SELECT CAST('12:05:00' AS TIME)",
+            },
+        )
+        self.validate_all(
+            "SELECT TO_TIME(CONVERT_TIMEZONE('UTC', 'US/Pacific', '2024-08-06 09:10:00.000')) AS pst_time",
+            write={
+                "snowflake": "SELECT TO_TIME(CONVERT_TIMEZONE('UTC', 'US/Pacific', '2024-08-06 09:10:00.000')) AS pst_time",
+                "duckdb": "SELECT CAST(CAST('2024-08-06 09:10:00.000' AS TIMESTAMP) AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific' AS TIME) AS pst_time",
+            },
+        )
+        self.validate_all(
+            "SELECT TO_TIME('11.15.00', 'hh24.mi.ss')",
+            write={
+                "snowflake": "SELECT TO_TIME('11.15.00', 'hh24.mi.ss')",
+                "duckdb": "SELECT CAST(STRPTIME('11.15.00', '%H.%M.%S') AS TIME)",
+            },
+        )
+        self.validate_all(
+            "SELECT TRY_TO_TIME('11.15.00', 'hh24.mi.ss')",
+            write={
+                "snowflake": "SELECT TRY_TO_TIME('11.15.00', 'hh24.mi.ss')",
+                "duckdb": "SELECT CAST(STRPTIME('11.15.00', '%H.%M.%S') AS TIME)",
+            },
+        )
+
     def test_semi_structured_types(self):
         self.validate_identity("SELECT CAST(a AS VARIANT)")
         self.validate_identity("SELECT CAST(a AS ARRAY)")