topic Re: Is this normal? Date column shows as datetime in Fabric UI in Data Engineering

Is this normal? Date column shows as datetime in Fabric UI

arlindTrystar — Fri, 23 Aug 2024 13:02:07 GMT

Is this expected?

Screenshot from Fabric Lakehouse UI

Same data from Fabric PySpark Notebook:

And schema:

Why is this?

Re: Is this normal? Date column shows as datetime in Fabric UI

lbendlin — Fri, 23 Aug 2024 15:38:43 GMT

There is no such thing as a Date data type. Everything is always DateTime. When you ask for "Date" Power BI and Fabric will give you a DateTime value pegged at midnight UTC.

Re: Is this normal? Date column shows as datetime in Fabric UI

frithjof_v — Sat, 24 Aug 2024 07:06:52 GMT

Interesting. Could you reference to some docs which explain this?

I still think it's a bad user experience to show the 12:00:00 AM in each cell when the data type is set to Date in the delta table.

It adds cognitive load to the user.

Re: Is this normal? Date column shows as datetime in Fabric UI

lbendlin — Sat, 24 Aug 2024 10:35:47 GMT

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types

Wait until you learn about Int128 ...

Re: Is this normal? Date column shows as datetime in Fabric UI

frithjof_v — Sat, 24 Aug 2024 21:41:46 GMT

Nice, thanks for sharing!

I'm curious to learn about the Int128 case!

However, the OP is showing the Lakehouse explorer, not a Power BI semantic model. So I'm not sure if the Power BI documentation is relevant for this case.

And OP has shown that the data type is date (OrderDate: date (nullable = true)).

So it seems that the Lakehouse explorer is formatting date type as date/time?

Re: Is this normal? Date column shows as datetime in Fabric UI

lbendlin — Sat, 24 Aug 2024 22:15:32 GMT

They use the same/similar engine, so they are constrained to the same data types.

Wait until you learn about how DateTimeZone is represented 🙂

Re: Is this normal? Date column shows as datetime in Fabric UI

frithjof_v — Sat, 24 Aug 2024 22:32:33 GMT

I did a similar test, and got the same results as the OP:

Notebook user interface:

Lakehouse user interface:

Re: Is this normal? Date column shows as datetime in Fabric UI

frithjof_v — Sun, 25 Aug 2024 17:50:10 GMT

"They use the same/similar engine, so they are constrained to the same data types."

Is there some documentation or information regarding this somewhere? It would be interesting to learn more about it.

Could you explain the DateTimeZone data type? The way I understand it, it gets converted to plain DateTime when it loads from Power Query into the Power BI semantic model. Does it basically just ignore the timezone component when loading from Power Query into Power BI?

About date. It is listed in the data type dropdown in Power BI.

https://learn.microsoft.com/en-us/power-bi/connect-data/media/desktop-data-types/pbiddatatypesindatareportview.png

Is this data type dropdown really just a formatting option, not an actual data type option?

I guess this video touches upon the same issue: https://youtu.be/SmczqYv_rfY?si=QuHnhBJzb4LIridk

And this article also: Power BI’s Pseudo-Time & Pseudo-Date Types | Ben Gribaudo

Does it depend on which data types we switch between? So choosing text in the data type dropdown would actually change the data type, whereas choosing date would just change the formatting of the datetime value?