Query with CAST unix TIMESTAMP as datetime (Native SQL)

Native SQL — the SQL runs on the BigQuery side (server-side). Prefix with #DirectSQL to use this mode. Shows how to return a timestamp column as date or datetime using CAST(col_timestamp AS DATE) or CAST(col_timestamp AS DATETIME).

#DirectSQL 
SELECT id, col_timestamp, CAST(col_timestamp AS DATE) AS timestamp_as_date, CAST(col_timestamp AS DATETIME) AS timestamp_as_datetime
FROM MyProject.MyDataset.MyTable
LIMIT 10