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!
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;
A shortcut to anyone who is trying to convert a Firebase event_timestamp in BigQuery:
SELECT
TIMESTAMP_SECONDS(CAST(CAST(event_timestamp as INT64)/1000000 as INT64)) as converted,
event_timestamp,
event_date
FROM [table] LIMIT 10