topic Re: Timestamp in databricks are getting converted to different timezone in Data Engineering

Timestamp in databricks are getting converted to different timezone

Dinu2 — Thu, 08 Jun 2023 07:43:15 GMT

Timestamp columns which are extracted from source databases using jdbc read are getting converted to different timezone and is not matching with source timestamp. Could anyone suggest how can we get same timestamp data like source data?

Re: Timestamp in databricks are getting converted to different timezone

-werners- — Thu, 08 Jun 2023 08:10:14 GMT

can you check this?

Re: Timestamp in databricks are getting converted to different timezone

Dinu2 — Thu, 08 Jun 2023 18:28:57 GMT

Thank You, if I am keeping cluster timezone as "UTC" it is converting all the timestamp to UTC. What I am looking is to get timestamp field value in source same in dataframe. Could you please let me know if you have any suggestions?

Re: Timestamp in databricks are getting converted to different timezone

Anonymous — Tue, 13 Jun 2023 14:57:12 GMT

@Werner Stinckens : Would you like to take this ahead? 🙂

Re: Timestamp in databricks are getting converted to different timezone

Anonymous — Tue, 13 Jun 2023 14:58:35 GMT

@Dinu Sukumara : My take on your question -

If you want to preserve the timestamp values exactly as they are in the source database, without any timezone conversion, you can follow these steps:

Set the Cluster Timezone: Keep the cluster timezone as UTC, as you mentioned.
Adjust Session Timezone: Before reading the data from the source database, you can adjust the session timezone in Databricks to match the timezone of the source data.

spark.conf.set("spark.sql.session.timeZone", "<source_timezone>")

3 . Read Data from Source: Use the JDBC read functionality in Databricks to extract the data from the source database.

df = spark.read.format("jdbc").option("url", "<jdbc_url>").option("dbtable", "<table_name>").load()

Re: Timestamp in databricks are getting converted to different timezone

-werners- — Tue, 13 Jun 2023 15:02:38 GMT

Besides the options mentioned earlier there is also the convert_timezone function.

Re: Timestamp in databricks are getting converted to different timezone

Anonymous — Wed, 14 Jun 2023 06:34:16 GMT

Hi @Dinu Sukumara

We haven't heard from you since the last response from @Werner Stinckens . Kindly share the information with us, and in return, we will provide you with the necessary solution.

Thanks and Regards

Re: Timestamp in databricks are getting converted to different timezone

Dinu2 — Wed, 14 Jun 2023 08:27:47 GMT

Hi @Vidula Khanna @Suteja Kanuri @Werner Stinckens after changing the tz on cluster level to UTC I am getting the source data as expected, but where I am using current timestamp in databricks I need to use from_utc_timestamp function. that requires more changes. So I am still checking if there are any other workarounds? Also can we give TZ o databricks database level or so? Any suggestions please?