CODEWITHSUNDEEP
sqlgoogle-bigquery
Joseph Wolf
Joseph Wolf

Reputation: 123

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

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!)

Upvotes: 8

Views: 34028

Answers (4)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

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

SELECT EXTRACT(DATE FROM TIMESTAMP_MILLIS(1494865480000))

Upvotes: 12

Felipe Hoffa
Felipe Hoffa

Reputation: 59245

A simpler way with TIMESTAMP_MILLIS():

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

2017-05-15

Upvotes: 4

Joseph Wolf
Joseph Wolf

Reputation: 123

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!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 0

Related Questions