WhatTheFact_Logo_Transparent_353x76px

Convert UNIX Timestamp to DATE or DATETIME format in Power BI

This post is also available in: Deutsch

If you find a date or datetime format in UNIX format, the first thing you wonder is what it is all about or how you can display it “normally”. This is because the UNIX date format looks like this:

1604135115

What exactly is UNIX Timestamp?

To find a way to convert this, we first need to understand what the number means. The UNIX timestamp is the number of seconds since January 01, 1970. You can read exactly about why it is like this in the Wikipedia article. I want to focus on finding a solution for this problem.

In the above example, 1604135115 seconds after 01 January 1970 is 31 October 2020 at 09:05 AM and 15 seconds.

Update February 20, 2023: The classic UNIX timestamp is, as said, the number of seconds since 01/01/1970. However, there are some programming languages and APIs that specify the timestamp in milliseconds since 01/01/1970. In such a case, the solutions given below must simply be divided by 1000. Some examples of this can be found at the end of the article. The timestamp for a value in 2023 is then not 10-digit as indicated above, but 13-digit. However, this is not a classic UNIX timestamp.

Transformation in Power Query

As a test scenario, we use the following system logs, since UNIX timestamp often appears in logs or similar technical data:

Data with UNIX timestamp as date and time format
Data with UNIX timestamp as date and time format

The easiest way to convert the UNIX timestamp is to do exactly what the UNIX timestamp does. We add the specified number of seconds to January 01, 1970. To do this, we create the date 01 January 1970 as a new column using the #datetime function and then add the number of seconds using the #durantion function:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp])
Add new column as DateTime
Add new column as DateTime

The new column contains the specific date and time as datetime format:

New column in datetime format
New column in datetime format

The original TimeStamp column can now be deleted if it is not needed in the report. From now on we can continue working with the real date.

Converting UNIX Timestamp with DAX within Power BI

If a conversion to Power Query would not be possible for some reason, then there is still the possibility to do this in DAX. My first option would be to do this as a Calculated Column. However, I would generally do this conversion directly in Power Query, since it is part of the data loading and transformation process.

However, we need one more calculation than in Power Query. Let’s start again by creating the initial date 01 January 1970 with the DATE function:

DATE(1970, 01, 01)

Now we have to add the timestamp seconds. However, this is not possible by simply using the TIME function. We must therefore first calculate the timestamp down to the number of days and the time:

UnixTimestamp = tTimestamp_DAX[TimeStamp] / ( 60 * 60 * 24 ) 

Finally, we need to add to the start value the time duration calculated down to days and time:

DateTime = 
VAR UnixTimestamp = tTimestamp_DAX[TimeStamp] / ( 60 * 60 * 24 ) 
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp

We can now use the new column in the report. We should hide the timestamp column for a better overview in the frontend:

image 7
Calculation of Timestamp to DateTime in DAX

Modifications for timestamp in millisecond format

As stated in the update above, there are also programming languages that specify the timestemp in milliseconds instead of seconds since 01/01/1970. In this case, the formulas change as follows:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp] / 1000 )

Or for the DAX approach:

DateTime = 
VAR UnixTimestamp = ( tTimestamp_DAX[TimeStamp] / 1000 ) / ( 60 * 60 * 24 ) 
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp

Share:

LinkedIn
Twitter
WhatsApp
Telegram
Email
Print
Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and principal consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

5 3 votes
Article Rating
Subscribe
Notify of
guest
11 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Cesare
Cesare
1 year ago

It looks like the correct formula is:
#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp] \ 1000)

If you don’t divide by 1000 the Unix date, you get an error. 🙂

Last edited 1 year ago by Cesare
Nilesh
Nilesh
6 months ago

I have imported data from New Relic Web API, it consist of column as timestamp
I have used custom column to convert timestamp into date as per the
#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [timestamp])) but it gives error

I have used calculated column by using
DateTime1 =
VAR UnixTimestamp = ‘Synthetic Check'[timestamp] / ( 60 * 60 * 24 )
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp
12/31/9999 11:59:59 PM

Nilesh
Nilesh
6 months ago
Reply to  Nilesh

timestamp number length is 13 digit
1.67663E+12
1676629251256

Tracy
Tracy
6 months ago

Thank you thank you so so much. This is my lifesaver. I so struggled with it since I’m having a test from my recruiter and it’s stuck cuz I can’t open the Query Editor, the file was connected thru their local. While all of the tutorials on the internet just mentioned it, finally I found your article showing another solution!! Thank you so so muchhhh

Nelson
Nelson
27 days ago

Hi!..I’m new in power BI world… I need to convert this kind of timestamp but I need to do this for a column who store lists, otherwise when I expand this column I have to convert 30 columns individually and I’ts a repetitive task. Do you know how to build a formula to multiple columns or a column with lists?

Captura2.JPG
Last edited 27 days ago by Nelson
Sylvanus
Sylvanus
13 days ago

Thanks a lot! It worked.

11
0
Would love your thoughts, please comment.x
()
x
Wait!

Follow me on LinkedIn and you’ll gain access to valuable insights, expert tips, and industry updates related to Power BI. Stay ahead of the curve and supercharge your analytics skills today!

Supercharge your Power BI skills!