10 Nov 18 · DataFreak ·       Add to Favorites  

Unix time to datetime in google bigquery?

I'm trying to transfer a postgres query to Google BigQuery but I can't seem to find out how to transfer this part:

date_trunc('Month',to_timestamp(created_utc)) 

Solution:

Below is for BigQuery Standard SQL

DATE_TRUNC(DATE(TIMESTAMP_SECONDS(created_utc)), MONTH)

example to test / play with

#standardSQL
SELECT
  DATE_TRUNC(DATE(TIMESTAMP_SECONDS(created_utc)), MONTH)
FROM `fh-bigquery.reddit_comments.2017_08`
LIMIT 5

DataFreak

posted on 10 Nov 18

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

Copied