10 November 2018 · DataFreak ·       Add to Favorites   Report

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 November 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied