Forum Discussion
datetime2 shows timestamp
Hi smpa01 ,
Thanks for reaching out to the Microsoft Fabric Community.
Since you're using Dataflow Gen2 to ingest data into the Lakehouse via CI/CD and don’t have access to the Spark runtime, options like setting spark.sql.session.timeZone or using Spark-based formatting aren’t available in your case.
The difference you're seeing between the Lakehouse preview UI and the SQL Endpoint comes down to how each one handles timestamps. The SQL Endpoint shows full UTC precision (datetime2), while the Lakehouse UI might only show the time portion or adjust it based on your local timezone settings this depends on your client environment.
To keep things consistent without relying on Spark, you can use a Derived Column in your Dataflow Gen2 to format the timestamp as a string like this:
formatDateTime([timestamp_column], 'yyyy-MM-dd HH:mm:ss')This approach ensures the full datetime is preserved during ingestion and avoids any display inconsistencies in the UI.
If you need precise values for validation or downstream processes, querying through the SQL Endpoint is your best.
Lastly, as the Lakehouse UI formatting behavior is by design, feel free to submit or upvote an idea here: https://ideas.fabric.microsoft.com
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.
Hi smpa01
it looks like The Lakehouse is likely converting the UTC time from the SQL Endpoint to a different timezone (e.g., `IST` or another local timezone).
Set the Lakehouse Spark session to use UTC to match the SQL
spark.conf.set("spark.sql.session.timeZone", "UTC")
Use explicit formatting to show both date and time with fractional seconds:
df.select(
date_format("timestamp_column", "yyyy-MM-dd HH:mm:ss.SSSSSS").alias("formatted_ts")
).show(truncate=False)
