Unable to produce timestamp with milliseconds using to_timestamp function in spark SQL

Question

I'm trying to convert a string to timestamp using spark SQL in Apache Spark Pool in Azure Synapse with to_timestamp function.

Select to_timestamp('2009-06-12 01:07:22.024', 'yyyy-MM-dd HH:mm:ss.SSS')

The result I got was - 2009-06-12T01:07:22Z

I'm not sure why the result doesn't have milliseconds just like the mentioned format. Kindly help me with it and hopefully you can do it using this simple SELECT query, without the T and Z which I assume is referring to timezone(?).

Thanks.

Answer 1

When you convert the string into timestamp, the column is now treated as timestamp but the standard string representation is in a form with '2009-06-12T01:07:22Z'. If you prefer some date format, then use date_format function to create the string representation such as

spark.sql('''Select date_format(to_timestamp('2009-06-12 01:07:22.024', 'yyyy-MM-dd HH:mm:ss.SSS'), 'yyyy-MM-dd HH:mm:ss') as time''').show(truncate=False)
+-------------------+
|time               |
+-------------------+
|2009-06-12 01:07:22|
+-------------------+