cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rogerh
Helper II
Helper II

Convert 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 

Table.AddColumn(#"Removed other columns", "Created At Date"each #datetime(197011000) + #duration(000, [created_at]/1000))
and
Table.AddColumn(#"Removed other columns", "Created At Date"each #datetime(197011000) + #duration(000, [created_at]))
And other ideas I have found online but none seem to work
Table.AddColumn(#"Added custom", "Created at Test 2"each Duration.TotalSeconds([created_at]-datetime(1970,1,1,0,0,0)))
 
Is there a way of doing it with this type of string?
Thanks
Roger

 

1 ACCEPTED SOLUTION
Rogerh
Helper II
Helper II

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: 

DateTime.FromFileTime([Created At])-#duration(584388,0,0,0)
 

View solution in original post

7 REPLIES 7
Rogerh
Helper II
Helper II

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: 

DateTime.FromFileTime([Created At])-#duration(584388,0,0,0)
 

View solution in original post

Glad you got it to work @Rogerh 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
watkinnc
Super User
Super User

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))  


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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.

edhans_0-1619481907955.png

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. 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
watkinnc
Super User
Super User

Try Table.AddColumn(TableName, "New Column", each (DateTime.From([TicksColumn]/86400)) + #datetime(1970,1,1,0,0,0))

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
edhans
Super User
Super User

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:

edhans_0-1618851301650.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors