timestamp - 从 Bigquery : a preferable method 中的时间戳中提取日期

Question

给 Bigquery 专家的一个快速问题。

这里有两种使用标准SQL从Bigquery中的时间戳中提取日期的方法

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

哪一个更可取，为什么？谢谢!

Answer 1

这真的归结为个人喜好；一个并不优于另一个，因为它们具有相同的语义和性能。支持使用 EXTRACT 的论据是如果您在选择列表中提取其他日期/时间部分，它会反射(reflect)它们。例如:

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;

相比:

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;