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:

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

The new column contains the specific date and time as 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:

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
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. đ
Hey Cesare,
that is kind of a special case. In general a UNIX timestamp is in seconds, so the formula from the article works properly.
However some programming languages, for example JavaScript, use milliseconds instead of seconds. In a case like that you are right, then you have to divide by 1000.
That is then not a proper UNIX timestamp.
But thank you for the hint, I will add this to the article.
Best regards
Denis
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
timestamp number length is 13 digit
1.67663E+12
1676629251256
Hey Nilesh,
same problem as in the first comment. When the timestamp has 13 digits it’s in miliseconds instead of seconds.
Classical UNIX timestamp is in seconds, but sometimes miliseconds are used
In this case, you just have to divide by 1000.
So try this formula:
DateTime1 =
VAR UnixTimestamp = (‘Synthetic Check'[timestamp] / 1000 ) / ( 60 * 60 * 24 )
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp
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
Hello Tracy,
thank you for the feedback, I’m happy my article was helpful for you đ
Good luck with the test and best regards
Denis
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?
Hey Nelson,
you can create a Power Query function to do this. But anyway you should do it in Power Query.
How you would do that in your specific case is a little difficult to say as I don’t know how the data looks like. In general in cases like that, I would recommend that you do a post in the Power BI section of the Fabric Community:
Microsoft Power BI Community – Microsoft Fabric Community
There usually you get help quite fast, if you give all the required information and in an ideal case also an example file.
Let me know if that works for you.
Best regards
Denis
Thanks a lot! It worked.
Hey Sylvanus,
I happy the article helped and thank you for the feedback đ
Best regards
Denis