How can I get commit.committed_date from commit.committer.date.seconds on bigquery-public-data:github_repos.commits

user_10584758 0
Viewed 179

This question is about the Google Big-Query Github public data.

I'd like to get the committed_date on bigquery-public-data:github_repos.commits. Although there is only commit.committer.date.seconds, I don't know how to translate from commit.committer.date.seconds to committed_date, like YYYY-MM-DD.

Is there anyone who can help me?

google-bigquery git
1 Answers

Below is for BigQuery Standrad SQL 

#standardSQL
SELECT committer.date.seconds, 
  DATE(TIMESTAMP_SECONDS(committer.date.seconds)) AS committed_date
FROM `bigquery-public-data.github_repos.commits`
LIMIT 10

As you can see you can translate seconds into timestamp using TIMESTAMP_SECONDS() and then into date using DATE()

Same in BigQuery Legacy SQL will look like below 

#legacySQL
SELECT committer.date.seconds, 
  DATE(SEC_TO_TIMESTAMP(committer.date.seconds)) AS committed_date
FROM [bigquery-public-data:github_repos.commits]
LIMIT 10

Note: It is highly recommended to use Standard SQL with BigQuery - I am presenting version for Legacy SQL only because you referenced table in legacy way - so you might looking for it

user_52219440
Related