从 Bigquery 中的时间戳中提取日期:一种更可取的方法 [英] Extracting date from timestamp in Bigquery: a preferable method

问题描述

给 Bigquery 专家的一个简短问题.

A quick question to Bigquery gurus.

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

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!

推荐答案

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

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;

对比:

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;

这篇关于从 Bigquery 中的时间戳中提取日期:一种更可取的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！