07 Mar 19 · npack · #bigquery ·   Bookmark   ×

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 |
+-------------------------+

npack

posted on 07 Mar 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds