Why does spark convert to_timestamp to cast syntax

[hellozepp]

Before you file an issue
sqlglot.transpile(sql, read="postgres", write="spark")

Fully reproducible code snippet
presto sql as following:

select to_timestamp(1415792726123/1000)

My expected spark output:

select to_timestamp(1415792726123/1000)

Actual output:

SELECT CAST(FROM_UNIXTIME(1415792726123 / 1000) AS TIMESTAMP)

Spark's to_timestamp is supported since: 2.2.0, and my execution results are as follows:

+------------------------------------+
|to_timestamp((1415792726123 / 1000))|
+------------------------------------+
| 2014-11-12 19:45:...|
+------------------------------------+

24/09/10 14:16:36 INFO SparkUI: Stopped Spark web UI at http://192.168.15.150:4040

So, can Spark use to_timestamp directly?

[hellozepp]

In addition, in sqlglot.dialects.spark2._unix_to_time_sql, from_unixtime uses lowercase function, which should be unified into uppercase.

[VaggelisD]

Hey @hellozepp, thanks for the report.

Regarding TO_TIMESTAMP, this is the PR that introduced it #1929. Is there any issue that arises with the cast solution? Although it could be roundtripped as you mention, I see that they produce the same results:

spark-sql (default)> SELECT CAST(1415792726123 / 1000 AS TIMESTAMP);
2014-11-12 13:45:26.123

spark-sql (default)> select to_timestamp(1415792726123/1000);
2014-11-12 13:45:26.123

Regarding FROM_UNIXTIME, the function name will be capitalized at the end, but if we raise a PR to preserve the roundtrip of TO_TIMESTAMP we can also change that for consistency purposes.

[hellozepp]

@VaggelisD yes, just for consistency purposes

[VaggelisD]

Thanks again for the concern, I'll go ahead and close this then as the semantics of the roundtrip are preserved.

If you come across a use case where this cast (or in general, any such transformation) breaks the roundtrip we can reconsider the refactor, otherwise it's fine for now.