Bigquery - select timestamp as human readable datetime

Question

How to select timestamp(stored as seconds) as human readable datetime in Google Bigquery?

schema

id(STRING) | signup_date(TIMESTAMP)

I wrote a query using DATE function, but getting error

SELECT DATE(create_date) FROM [accounts] 

Error: Invalid function name: DATE; did you mean CASE?

Thanks!

Answer 1

I think I found a working solution from Bigquery reference page. Basically BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision.

SELECT SEC_TO_TIMESTAMP(date) FROM ...