从Bigquery中的时间戳中提取日期:一种更可取的方法 [英] Extracting date from timestamp in Bigquery: a preferable method
本文介绍了从Bigquery中的时间戳中提取日期:一种更可取的方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Bigquery专家快速提问.
A quick question to Bigquery gurus.
以下是使用standardSQL从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屋!
查看全文