Timestamp in databricks are getting converted to different timezone
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-08-2023 12:43 AM
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?
- Labels:
-
Source Data
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-08-2023 01:10 AM
can you check this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-08-2023 11:28 AM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-13-2023 07:57 AM
@Werner Stinckens : Would you like to take this ahead? 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-13-2023 07:58 AM
@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()
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-13-2023 08:02 AM
Besides the options mentioned earlier there is also the convert_timezone function.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-13-2023 11:34 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-14-2023 01:27 AM
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?
![](/skins/images/8C2A30E5B696B676846234E4B14F2C7B/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/8C2A30E5B696B676846234E4B14F2C7B/responsive_peak/images/icon_anonymous_message.png)