Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good afternoon Power Community,
I am connected to a Azure SQL server. I am getting a time stamp in Ticks was woundering if anyone knew how to convert it to datetime?
An example of what is in the 'Created at' columns : 636905005268967000
Edit: Before I knew it was 'Ticks' I had tried the below:
I've tried Unix time
Solved! Go to Solution.
Sorry for the slow reply and thank you for all of your replies @edhans @watkinnc. I tried a number of options and the following is how I got it to work:
I created a custom columns and used the following code:
Sorry for the slow reply and thank you for all of your replies @edhans @watkinnc. I tried a number of options and the following is how I got it to work:
I created a custom columns and used the following code:
Glad you got it to work @Rogerh
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWell...UNIX time has the granularity of seconds. Each UNIX time integer is the number of seconds (or "ticks") since 1/1/1970. Each 86,400 interval is a full day. That's why MOD(UNIX time/86400) will give you the decimal time since last midnight.
Hence
Table.AddColumn(TableName, "New Column", each (DateTime.From([TicksColumn]/86400)) + #datetime(1970,1,1,0,0,0))
I don't think it is unix time. @Rogerh said they were ticks. And that isn't the same thing. It is the number of 100 nanosecond increments since the beginning of the 21st century.
Even if it was UNIX time your formula doesn't work. You cannot divide 636905005268967000 by 86400 then use DateTime.From() around that.
If it is Unix Time in some way (which I do not think it is as unix time is in seconds), use this article to resolve. Working with Unix Epoch Time In Power BI — ehansalytics
But I think these are ticks, and entirely different from Unix Time seconds. Hence, the solution I provided in the first response, which resolved to April 9, 2018. It seemed to me to be a reasonable answer for data someone would be working on today. But until @Rogerh replies, I'm just assuming my formula worked. But my formula did resolve. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTry Table.AddColumn(TableName, "New Column", each (DateTime.From([TicksColumn]/86400)) + #datetime(1970,1,1,0,0,0))
--Nate
If I understand ticks, they are since Jan 1, 2020 (beginning of the 21st century) and at the 1/10,000,000 of a second level, so divide the number you have by 10 million so you are working with seconds, then use this formula to convert back to a time:
#datetime(2000,1,1,0,0,0) + #duration(0,0,0,Number.Round([Ticks] / 10000000,0)))
It returns this:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Rogerh - my formula given above worked in my tests. Did you try it with your data?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting