CODEWITHSUNDEEP
sqlgoogle-bigquery
Maykid
Maykid

Reputation: 517

How do you convert epoch to date in BigQuery?

So what I'm trying to do is simply convert epoch to date format. The inputs I get varies quite a bit,

1518121195039482
1518122508171529
1518120794058543
1518124107950252

All of these are in microseconds. What I'm wondering is how do I convert this instead of microseconds turn it into an actual date like:

2018-07-28
2018-07-01
2018-07-10

I've tried doing a couple different queries but haven't quite got it yet, keeps either erroring out or errors out before I can run the query.

Any help would be great! If anyone needs more information please let me know and I'll try to explain it as best as I can.

-Maykid

Upvotes: 2

Views: 10124

Answers (3)

user11683225
user11683225

Reputation: 1

Solution for Google BigQuery SQL:

 SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

in BigQuery Standard SQL you should use TIMESTAMP_MICROS() function for this as in example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1518121195039482 ts UNION ALL
  SELECT 1518122508171529 UNION ALL
  SELECT 1518120794058543 UNION ALL
  SELECT 1518124107950252 
)
SELECT ts, TIMESTAMP_MICROS(ts)
FROM `project.dataset.table`

in BigQuery Legacy SQL - you can use USEC_TO_TIMESTAMP() for this as in example below 

#legacySQL
SELECT ts, USEC_TO_TIMESTAMP(ts)
FROM 
  (SELECT 1518121195039482 ts),
  (SELECT 1518122508171529 ts),
  (SELECT 1518120794058543 ts),
  (SELECT 1518124107950252 ts)

both return: 

Row ts                  f0_  
1   1518121195039482    2018-02-08 20:19:55.039482 UTC   
2   1518122508171529    2018-02-08 20:41:48.171529 UTC   
3   1518120794058543    2018-02-08 20:13:14.058543 UTC   
4   1518124107950252    2018-02-08 21:08:27.950252 UTC

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You want TIMESTAMP_MICROS():

select timestamp_micros(1518121195039482)

There are also functions: timestamp_millis() and timestamp_seconds() depends on what you mean by "epoch".

Upvotes: 4

Related Questions