Converting SAP or Unix time stamp

[brett_walton]

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 

[v-yuezhe-msft · Accepted solution]

Hi @brett_walton，

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

[brett_walton]

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