How to convert an Epoch timestamp to a Date in Standard SQL

Question

I didn't find any simple answer to this while I was looking around, so I thought I'd put it up here in case anyone was having the same problem as me with what should have been a trivial issue.

I was using ReDash analytics with Google's BigQuery and had turned on Standard SQL in the datasource settings. For the purposes of my query, I needed to convert a timestamp - unix time in milliseconds, as a string - to a Date format so that I could use the DATE_DIFF method.

As an example... "1494865480000" to "2017-05-15"

The difficulty was that casting and conversion was excessively strict and there seemed no adequate way to make it parse. See my answer down below! (Though let me know if some SQL sensei knows a more eloquent way!)

Answer 1

In Standard SQL use TIMESTAMP_MICROS function together with EXTRACT(DATE FROM <timestamp>):

SELECT EXTRACT(DATE FROM TIMESTAMP_MILLIS(1494865480000))

Answer 2

A simpler way with TIMESTAMP_MILLIS():

#standardSQL
SELECT DATE(TIMESTAMP_MILLIS(CAST("1494865480000" AS INT64)))

2017-05-15