Repository: tobymao/sqlglot

Issue:
FROM_UNIXTIME has different types in Trino and SparkSQL
```FROM_UNIXTIME(`created`)``` in Trino SQL returns a datetime, but ```FROM_UNIXTIME(`created`)``` in SparkSQL returns a string, which can cause queries to fail.

I would expect:
```select FROM_UNIXTIME(`created`) from a``` # (read='trino') 
to convert to
```select CAST(FROM_UNIXTIME('created') as timestamp) from a``` # (write='spark')

https://docs.databricks.com/sql/language-manual/functions/from_unixtime.html
https://spark.apache.org/docs/3.0.0/api/sql/#from_unixtime


Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 3c2182ff..bce956eb 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -168,6 +168,9 @@ class DuckDB(Dialect):
             exp.ArraySort: _array_sort_sql,
             exp.ArraySum: rename_func("LIST_SUM"),
             exp.CommentColumnConstraint: no_comment_column_constraint_sql,
+            exp.CurrentDate: lambda self, e: "CURRENT_DATE",
+            exp.CurrentTime: lambda self, e: "CURRENT_TIME",
+            exp.CurrentTimestamp: lambda self, e: "CURRENT_TIMESTAMP",
             exp.DayOfMonth: rename_func("DAYOFMONTH"),
             exp.DayOfWeek: rename_func("DAYOFWEEK"),
             exp.DayOfYear: rename_func("DAYOFYEAR"),
diff --git a/sqlglot/dialects/spark2.py b/sqlglot/dialects/spark2.py
index 6056940e..1e62417d 100644
--- a/sqlglot/dialects/spark2.py
+++ b/sqlglot/dialects/spark2.py
@@ -42,7 +42,7 @@ def _unix_to_time_sql(self: Hive.Generator, expression: exp.UnixToTime) -> str:
     scale = expression.args.get("scale")
     timestamp = self.sql(expression, "this")
     if scale is None:
-        return f"FROM_UNIXTIME({timestamp})"
+        return f"CAST(FROM_UNIXTIME({timestamp}) AS TIMESTAMP)"
     if scale == exp.UnixToTime.SECONDS:
         return f"TIMESTAMP_SECONDS({timestamp})"
     if scale == exp.UnixToTime.MILLIS:


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 386b0900..80edcd0a 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -187,7 +187,6 @@ class TestBigQuery(Validator):
             "current_datetime",
             write={
                 "bigquery": "CURRENT_DATETIME()",
-                "duckdb": "CURRENT_DATETIME()",
                 "presto": "CURRENT_DATETIME()",
                 "hive": "CURRENT_DATETIME()",
                 "spark": "CURRENT_DATETIME()",
@@ -197,7 +196,7 @@ class TestBigQuery(Validator):
             "current_time",
             write={
                 "bigquery": "CURRENT_TIME()",
-                "duckdb": "CURRENT_TIME()",
+                "duckdb": "CURRENT_TIME",
                 "presto": "CURRENT_TIME()",
                 "hive": "CURRENT_TIME()",
                 "spark": "CURRENT_TIME()",
@@ -207,7 +206,7 @@ class TestBigQuery(Validator):
             "current_timestamp",
             write={
                 "bigquery": "CURRENT_TIMESTAMP()",
-                "duckdb": "CURRENT_TIMESTAMP()",
+                "duckdb": "CURRENT_TIMESTAMP",
                 "postgres": "CURRENT_TIMESTAMP",
                 "presto": "CURRENT_TIMESTAMP",
                 "hive": "CURRENT_TIMESTAMP()",
@@ -218,7 +217,7 @@ class TestBigQuery(Validator):
             "current_timestamp()",
             write={
                 "bigquery": "CURRENT_TIMESTAMP()",
-                "duckdb": "CURRENT_TIMESTAMP()",
+                "duckdb": "CURRENT_TIMESTAMP",
                 "postgres": "CURRENT_TIMESTAMP",
                 "presto": "CURRENT_TIMESTAMP",
                 "hive": "CURRENT_TIMESTAMP()",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 1a4c78bd..c7e6e85c 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -6,6 +6,9 @@ class TestDuckDB(Validator):
     dialect = "duckdb"
 
     def test_time(self):
+        self.validate_identity("SELECT CURRENT_DATE")
+        self.validate_identity("SELECT CURRENT_TIMESTAMP")
+
         self.validate_all(
             "EPOCH(x)",
             read={
@@ -24,7 +27,7 @@ class TestDuckDB(Validator):
                 "bigquery": "UNIX_TO_TIME(x / 1000)",
                 "duckdb": "TO_TIMESTAMP(x / 1000)",
                 "presto": "FROM_UNIXTIME(x / 1000)",
-                "spark": "FROM_UNIXTIME(x / 1000)",
+                "spark": "CAST(FROM_UNIXTIME(x / 1000) AS TIMESTAMP)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 30804768..c657dfd8 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -133,6 +133,14 @@ class TestPresto(Validator):
         self.validate_identity("TRIM(a, b)")
         self.validate_identity("VAR_POP(a)")
 
+        self.validate_all(
+            "SELECT FROM_UNIXTIME(col) FROM tbl",
+            write={
+                "presto": "SELECT FROM_UNIXTIME(col) FROM tbl",
+                "spark": "SELECT CAST(FROM_UNIXTIME(col) AS TIMESTAMP) FROM tbl",
+                "trino": "SELECT FROM_UNIXTIME(col) FROM tbl",
+            },
+        )
         self.validate_all(
             "DATE_FORMAT(x, '%Y-%m-%d %H:%i:%S')",
             write={
@@ -181,7 +189,7 @@ class TestPresto(Validator):
                 "duckdb": "TO_TIMESTAMP(x)",
                 "presto": "FROM_UNIXTIME(x)",
                 "hive": "FROM_UNIXTIME(x)",
-                "spark": "FROM_UNIXTIME(x)",
+                "spark": "CAST(FROM_UNIXTIME(x) AS TIMESTAMP)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 78f23e49..57ee2354 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -227,7 +227,7 @@ class TestSnowflake(Validator):
             write={
                 "bigquery": "SELECT UNIX_TO_TIME(1659981729)",
                 "snowflake": "SELECT TO_TIMESTAMP(1659981729)",
-                "spark": "SELECT FROM_UNIXTIME(1659981729)",
+                "spark": "SELECT CAST(FROM_UNIXTIME(1659981729) AS TIMESTAMP)",
             },
         )
         self.validate_all(
@@ -243,7 +243,7 @@ class TestSnowflake(Validator):
             write={
                 "bigquery": "SELECT UNIX_TO_TIME('1659981729')",
                 "snowflake": "SELECT TO_TIMESTAMP('1659981729')",
-                "spark": "SELECT FROM_UNIXTIME('1659981729')",
+                "spark": "SELECT CAST(FROM_UNIXTIME('1659981729') AS TIMESTAMP)",
             },
         )
         self.validate_all(
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 7d1a0910..9753e73d 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -479,7 +479,7 @@ FROM v""",
         self.validate("UNIX_TO_STR(123, 'y')", "FROM_UNIXTIME(123, 'y')", write="spark")
         self.validate(
             "UNIX_TO_TIME(123)",
-            "FROM_UNIXTIME(123)",
+            "CAST(FROM_UNIXTIME(123) AS TIMESTAMP)",
             write="spark",
         )
         self.validate(