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  

Answer 3

After much trial and error, this was my solution:

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

That is, I took the string, cast it to an integer, divided it by the number of milliseconds in a day, then used a DATE_ADD method, and added the result to the start of Epoch time, and calculated the resulting day.

I hope this saves another junior some time!

Answer 4

Use UTC_USEC_TO_TIMESTAMP():

select UTC_USEC_TO_TIMESTAMP(postedon * 1000)

You can then extract the date using date():

select DATE(UTC_USEC_TO_TIMESTAMP(postedon * 1000))

This doesn't require knowing the internal format of Unix timestamps.