How to convert epoch (seconds since 1970) to date in Google BigQuery ?

What is epoch time?

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds.

For example, to represent today's date in epoch it would be a sum of all the seconds from 1970-01-01 till now

How to convert EPOCH seconds to Date in Bigquery

TIMESTAMP_SECONDS function can be used to convert epoch seconds to date

SELECT TIMESTAMP_SECONDS(1230219000);
+-------------------------+
| timestamp_value         |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

How to convert EPOCH milliseconds to Date in Bigquery

TIMESTAMP_MILLIS function can be used to convert epoch milliseconds to date

SELECT TIMESTAMP_MILLIS(1230219000000)
+-------------------------+
| timestamp_value         |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

How to convert EPOCH microseconds to Date in Bigquery

TIMESTAMP_MICRO function can be used to convert epoch microseconds to date

SELECT TIMESTAMP_MICROS(1230219000000000)

+-------------------------+
| timestamp_value         |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

mCollins

posted on 07 Mar 19