Forum Discussion
Helper IIConvert Ticks timestamp to datetime - Power Query
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
Sorry for the slow reply and thank you for all of your replies edhans Anonymous. 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:
DateTime.FromFileTime([Created At])-#duration(584388,0,0,0)I found this here: Solved: Convert C# date ticks to DateTime - Microsoft Power BI Community
7 Replies
- edhans
Community Champion
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:
Well...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))
- edhans
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. 😁
RogerhSorry for the slow reply and thank you for all of your replies edhans Anonymous. 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:
DateTime.FromFileTime([Created At])-#duration(584388,0,0,0)I found this here: Solved: Convert C# date ticks to DateTime - Microsoft Power BI Community
