topic Re: datetime2 shows timestamp in Data Engineering

datetime2 shows timestamp

smpa01 — Thu, 22 May 2025 18:29:43 GMT

I have ingested data with df gen2 cicd in lakehouse., one of the columns is datetime2 and shows correct value in sql endpoint

But shows strange value in lakehouse

Is there reason for this? How can I have the lakehouse dispay the same datetime2 value as it is displayed by sql endpoint?

@miguel

Re: datetime2 shows timestamp

nilendraFabric — Thu, 22 May 2025 21:30:38 GMT

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)

Re: datetime2 shows timestamp

smpa01 — Fri, 23 May 2025 00:45:23 GMT

I am ingesting data in the lakehouse using dataflow gen2 cicd, I have no way to play with spark config

Re: datetime2 shows timestamp

smpa01 — Fri, 23 May 2025 14:49:48 GMT

I keep on getting the same values in

Lakehouse

vs sql endpoint

Re: datetime2 shows timestamp

Anonymous — Sun, 25 May 2025 01:14:59 GMT

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.

Re: datetime2 shows timestamp

Anonymous — Wed, 28 May 2025 05:32:30 GMT

Hi @smpa01 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.