Synapse - spark table over CSV - date not matching file

Ryan Abbey 1,171

We are seeing a really odd issue... we have a Spark table configured to read a CSV file, for a particular row in the file, we have a date column which is set to "2023-05-02T21:27:34.3299026Z". If we query that row via the table via Synapse Serverless, we get "2023-05-02 21:27:34", however, when querying that exact same table from within a Notebook session is giving us "2023-05-02T22:22:33Z"

The time discrepancy appears to vary across the records, anyone with any ideas as to how they are being read differently?

AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2023-05-04T07:15:26.4133333+00:00
Hi Ryan Abbey ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query.

I understand you are seeing discrepancies in the way date column is storing the data in notebook and in serverless table in synapse. Please let me know if that is not the correct understanding.

Could you please share the query you are using to fetch the data from serverless sql? Are you using openrowset or creating an external table?

It might be occuring due to the difference in default timezone settings in Synapse Serverless and the Notebook session.

Please try to match the timestamp explicitly by casting it to the required format. You can use to_timestamp() function from the pyspark library in your notebook and define the format .

df.select(to_timestamp(lit(column),'yyyy-MM-dd HH:mm:ss.SSSS')) \ .show()

For more details, kindly checkout the below posts:

PySpark to_timestamp() – Convert String to Timestamp type

datetime2 (Transact-SQL)
Ryan Abbey 1,171 Reputation points

2023-05-04T21:58:11.6033333+00:00
It's a Spark table so created via

using CSV LOCATION

The field itself is defined as a timestamp

However, we are currently going through a Spark 3.3 upgrade and this does not appear to be showing for Spark 3.3 (we are currently on 2.4) so looks to be an issue in the older version
AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee

2023-05-05T07:42:50.02+00:00

Hi Ryan Abbey ,

Thanks for sharing the updates. As you mentioned the issue might be cause due to the older version of spark being used.

Kindly checkout the following thread to know How to upgrade Spark version in Synaspe?

Please let us know if this helps in resolving the issue . Thanks