Convert UNIX time (INT) to timestamp in BigQuery

Question

I have a column "date_time" in a BigQuery table which contains unix timestamp values like "1569888224". The problem is that these values are integer data types, not timestamp data types, so I do not seem to have an easy way to convert them to human readable date/times. Does anyone have a good way of converting these integers into datetime values in BigQuery?

Thanks!

Answer 1

This can be solved by using BigQuery's TIMESTAMP_SECONDS function - https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#format_timestamp

select TIMESTAMP_SECONDS(date_time) from table;