Converting unixtime to date when importing

[Stroop]

I am importing data from a MySQL database but because it's too large I am using a ODBC Impala connection which connects to the MySQL tables I need quickly. 

In the MySQL table I am using there is a timestamp field. However when connecting via Impala and uploading the full table this appears to be in a unixtime format (integer of how many seconds since 1970). 

This is the SQL I have entered when trying to upload for that field

select

dateadd(S,[TIME],'1970-01-01') AS DATE,

other column,

case statement,

count(other columns),

sum(other columns).....

group by ......

however Power BI does not recognise dateadd as a function and errors when running. 

I have also tried from_unixtime(time) too but I get the same error

Details: "ODBC: ERROR [HY000] [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Syntax error in line 2:
from_unixtime(TIME) AS DATE,
^
Encountered: DATE
Expected: DEFAULT, IDENTIFIER

CAUSED BY: Exception: Syntax error

Can anybody help?

[v-yuezhe-msft · Accepted solution]

@Stroop,

Convert the timestamp field to datetime field in Query Editor or using DAX after you import the data to Power BI Desktop. For more details, please review the following similar thread.

https://community.powerbi.com/t5/Desktop/Converting-SAP-or-Unix-time-stamp/td-p/81628

Regards,
Lydia