Local timestamp issue with Synpase notebook

Question

Local timestamp issue with Synpase notebook

Lotus88 136

Hi,

I am using Synapse notebook to write a record with system update timestamp.

I wrote the following code in the beginning of the notebook code to adjust timestamp to UTC+8.

# Adjust timestamps to Asia/Singapore time
spark.conf.set("spark.sql.session.timeZone", "Asia/Singapore")

Then after all the db connection and query data to dataframes, I added a new system update timestamp field to the result_df.

result_df = select_df.withColumn("sys_update_dt", current_timestamp())

Then I write the records to a destination table. But when I checked the "sys_update_dt", it is populated with UTC timestamp. I expect UTC+8 timestamp. Does anyone have any idea what went wrong? Thanks in advance.

result_df.write.option("truncate", "true").jdbc(
    url=jdbc_url_db2,
    table="dest_stage",
    mode="overwrite",
    properties=conn_properties_db2
)

Accepted answer

0 additional answers

Your answer

Answer 1

Smaran Thoomu 22,840 Microsoft External Staff

Hi @Lotus88
Thanks for reaching out and for sharing the details of your notebook setup.

You have correctly set the session time zone using:

spark.conf.set("spark.sql.session.timeZone", "Asia/Singapore")

However, the function current_timestamp() still returns the timestamp in UTC regardless of this setting, especially in PySpark contexts. This setting mainly affects Spark SQL queries, not necessarily PySpark API functions.

To ensure the timestamp reflects the correct local time (UTC+8), you can explicitly convert it using:

from pyspark.sql.functions import current_timestamp, from_utc_timestamp
result_df = select_df.withColumn(
    "sys_update_dt", from_utc_timestamp(current_timestamp(), "Asia/Singapore")
)

User's image

This ensures that the timestamp column reflects UTC+8 accurately when written to your destination.

Let me know if you would like help validating this with your specific destination system. I’ve also attached screenshots showing the behavior and the fix in action for reference.

Hope this helps! Please feel free to reach out if you have any more questions.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Lotus88 136 Reputation points

2025-04-30T03:27:40.9233333+00:00
Actually, my df show result already displayed the timestamp in UTC+8 but when using the write option to my Azure DB, timestamp still in UTC. This is very strange. I thought whatever it is display, the same value should written in DB. It is 11:44 SGT now.

result_df = select_df.withColumn("sys_update_dt", current_timestamp()) result_df.show(5)

The result look correct. But write to DB table it is UTC. Is this the expected result?

# Write the delta changes dataset to stage table result_df.write.option("truncate", "true").jdbc( url=jdbc_url_db2, table="dbo.stage", mode="overwrite", properties=conn_properties_db2 ) print("Write to stage table successfully!")

This is my DB result.
Smaran Thoomu 22,840 Reputation points Microsoft External Staff

2025-04-30T07:37:23.17+00:00
Hi Lotus88

Thank you for the follow-up and for sharing the observations.

You're absolutely right - while the DataFrame's .show() method displays the timestamp adjusted to the configured time zone (Asia/Singapore), the actual value written to an external system like Azure SQL or Azure Database for PostgreSQL (via .jdbc()) gets serialized in UTC by default. This behavior is expected and is due to how JDBC drivers handle timestamp data during writes - typically, they use UTC for compatibility and consistency across systems.

To write the timestamp in UTC+8 to your database, you can explicitly convert the timestamp column before writing:

from pyspark.sql.functions import current_timestamp, from_utc_timestamp result_df = select_df.withColumn( "sys_update_dt", from_utc_timestamp(current_timestamp(), "Asia/Singapore") )

This approach ensures that the underlying value written to the DB reflects the Singapore local time, not just how it's displayed in the notebook.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Lotus88 136 Reputation points

2025-04-30T10:02:11.93+00:00

Ok thank you. In that case the display and DB data is not aligned. Thank you it is working now.
Smaran Thoomu 22,840 Reputation points Microsoft External Staff

2025-04-30T10:43:32.5933333+00:00

Lotus88 If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Local timestamp issue with Synpase notebook

0 additional answers

Your answer