cancel
Showing results for
Did you mean:
Helper II

## Converting SAP or Unix time stamp

SAP posted data with a time stamp that is seconds since 1/1/1990   example  8,461,585,290,000

What the best way to convert this to a date and time field

1 ACCEPTED SOLUTION
Microsoft

Firstly, according to my knowledge, the Unix time stamp is a whole number value, which is number of seconds from 1/1/1970 rather than from 1/1/1990.

Secondly, you can use one of the following two methods to covert Unix time stamp to date time.

1. In Query Editor of Power BI Desktop, add a custom column and specify that using the following formula. There is an example in the following screenshot for your reference.

#datetime(1970, 1, 1, 0, 0, 0 ) + #duration(0, 0, 0, [Timestampcolumn])

2. Create a calculated column in Data view of Power BI Desktop using the following formula.

sapdatetime = VAR sapDays = Table[Timestampcolumn]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+sapDays)

Thirdly, do you post the correct timestamp value? When I convert the example number 8,461,585,290,000 to date time, I get the following error message.

Besides, if you are sure that the timestamp is calculated from 1/1/1990, please replace 1/1/1970 with 1/1/1990 in the above formulas.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

Firstly, according to my knowledge, the Unix time stamp is a whole number value, which is number of seconds from 1/1/1970 rather than from 1/1/1990.

Secondly, you can use one of the following two methods to covert Unix time stamp to date time.

1. In Query Editor of Power BI Desktop, add a custom column and specify that using the following formula. There is an example in the following screenshot for your reference.

#datetime(1970, 1, 1, 0, 0, 0 ) + #duration(0, 0, 0, [Timestampcolumn])

2. Create a calculated column in Data view of Power BI Desktop using the following formula.

sapdatetime = VAR sapDays = Table[Timestampcolumn]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+sapDays)

Thirdly, do you post the correct timestamp value? When I convert the example number 8,461,585,290,000 to date time, I get the following error message.

Besides, if you are sure that the timestamp is calculated from 1/1/1990, please replace 1/1/1970 with 1/1/1990 in the above formulas.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Thank you this worked , I realize UNIX is 1970 was just using it as a reference. You are correct about the time stamp digging into it SAP multiples the number by 10,000 so I had to divide and it all worked out.

Thank you

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.