How to convert Epoch timestamp to date in standard SQL

Question

I did not find a simple answer to this question while I looked around, so I thought I would put it here if someone had the same problem as me with what should have been a trivial problem.

I used ReDash analytics with Google BigQuery and included Standard SQL in the data source settings. For the purposes of my query, I needed to convert the timestamp - the unix time in milliseconds, as a string - to the date format so that I could use the DATE_DIFF method.

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

The difficulty was that the casting and conversion were excessively strict, and there was no adequate way to make it parse. See my answer below! (Although let me know if any SQL sensei knows a more eloquent way!)

Answer 1

In standard SQL, use the function TIMESTAMP_MICROSalong with EXTRACT(DATE FROM <timestamp>):

SELECT EXTRACT(DATE FROM TIMESTAMP_MILLIS(1494865480000))

Answer 2

The simplest way with TIMESTAMP_MILLIS ():

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

2017-05-15  

Answer 3

:

DATE_ADD( DATE'1970-01-01', INTERVAL CAST( ( CAST( epochTimestamp AS INT64 ) / 86400000 ) AS INT64 ) DAY ) AS convertedDate

, , , DATE_ADD Epoch .

, - !

Answer 4

Use UTC_USEC_TO_TIMESTAMP():

select UTC_USEC_TO_TIMESTAMP(postedon * 1000)

Then you can extract the date using date():

select DATE(UTC_USEC_TO_TIMESTAMP(postedon * 1000))

This does not require knowledge of the internal Unix timestamp format.