CODEWITHSUNDEEP
timestampgoogle-bigquery
Dmitri Ilin
Dmitri Ilin

Reputation: 119

Extracting date from timestamp in Bigquery: a preferable method

A quick question to Bigquery gurus.

Here are two methods for extracting date from a timestamp in Bigquery using standardSQL

#standardSQL
#1
DATE(TIMESTAMP_MILLIS(CAST((timestamp) AS INT64)))
#2
EXTRACT(DATE FROM TIMESTAMP_MILLIS(timestamp))

Which one is more preferable and why? Thanks!

Upvotes: 5

Views: 11230

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

It really comes down to personal preference; one isn't superior to the other since they have the same semantics and performance. The argument in favor of using EXTRACT is that if you are extracting other date/time parts in the select list, it mirrors them. For example:

SELECT
  EXTRACT(DATE FROM TIMESTAMP_MILLIS(timestamp)) AS date,
  EXTRACT(ISOYEAR FROM TIMESTAMP_MILLIS(timestamp)) AS iso_year,
  EXTRACT(ISOWEEK FROM TIMESTAMP_MILLIS(timestamp)) AS iso_week
FROM YourTable;

Compared to:

SELECT
  DATE(TIMESTAMP_MILLIS(timestamp)) AS date,
  EXTRACT(ISOYEAR FROM TIMESTAMP_MILLIS(timestamp)) AS iso_year,
  EXTRACT(ISOWEEK FROM TIMESTAMP_MILLIS(timestamp)) AS iso_week
FROM YourTable;

Upvotes: 11

Related Questions