cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BenKoth Frequent Visitor
Frequent Visitor

DateTime in Azure Cosmos DB

I'm using an Azure Cosmos DB that is populated through the Azure Cosmos .NET SDK. According to the documentation, DateTimes should be stored as a serialize string in ISO_8601 format (https://docs.microsoft.com/en-us/azure/cosmos-db/working-with-dates). When I view my records in the azure portal or the azure storage explorer app, it appears like that is the case.image.png

 

You can see my StartTime and EndTime properties as well as the TimeStamp are in the format "2019-07-15T16:24:51.392Z"

 

When I import the data to Power BI Desktop, the value comes out as some bizarre string/number. e.g. 

00636988010188048801

 

I thought that perhaps it was the # of milliseconds since 1/1/1970, but that is not the case. Does anyone know how to get the date time imported into Power BI correctly or how to convert that value to a datetime? Trying to just set the data type to any of the various DateTime options results in an error.

1 ACCEPTED SOLUTION

Accepted Solutions
BenKoth Frequent Visitor
Frequent Visitor

Re: DateTime in Azure Cosmos DB

I was able to get a solution from the issue/idea I submitted.

let

ConvertTimestamp = (ts) => #datetime(1, 1, 1, 0, 0, 0) + #duration(0, 0, 0, ts / 10000000)

in

ConvertTimestamp(635912639960000000)

https://community.powerbi.com/t5/Issues/Support-DateTime-Populated-Through-Azure-Cosmos-Table-SDK/id...

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: DateTime in Azure Cosmos DB

Hi @BenKoth 

 

You can refer to this article to convert ISO format to date , and i have do a pbix for to to try.

Reference:https://community.powerbi.com/t5/Desktop/Convert-isoYearIsoWeek-YYYYWW-into-a-date/m-p/148556/highli...

here is pbix, the steps are in it.

https://www.dropbox.com/s/u8nbcwpno4ld1h9/Convert%20ISO%20%28WWYY%29%20format%20to%20a%20date.pbix?d...

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
BenKoth Frequent Visitor
Frequent Visitor

Re: DateTime in Azure Cosmos DB

Thanks. I don't think my data is actually in the ISO format like it claims. I don't know what format it is. Has anyone used the Azure Cosmos DB to get data that contains a date time?

 

My data is populated using the Azure Cosmos Table SDK, I wonder if that is where the disconnect is. You have to do some strange URL manipulation to change the table.cosmos to "documents" for the datasource URL.

BenKoth Frequent Visitor
Frequent Visitor

Re: DateTime in Azure Cosmos DB

I had submitted a similar question on the Azure Cosmos Table SDK Github page and it looks like they are purposely storing this date in an unknown format. I'm guessing they aren't going to change the format so I'm going to submit an issue to Power BI to add the ability to convert this format to DateTime. For reference:

https://github.com/Azure/azure-cosmos-table-dotnet/issues/14

 

BenKoth Frequent Visitor
Frequent Visitor

Re: DateTime in Azure Cosmos DB

I was able to get a solution from the issue/idea I submitted.

let

ConvertTimestamp = (ts) => #datetime(1, 1, 1, 0, 0, 0) + #duration(0, 0, 0, ts / 10000000)

in

ConvertTimestamp(635912639960000000)

https://community.powerbi.com/t5/Issues/Support-DateTime-Populated-Through-Azure-Cosmos-Table-SDK/id...

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 143 members 1,616 guests
Please welcome our newest community members: