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