sql - 谷歌 bigquery 从现在到 n 天前的时间戳列中选择

Question

我在 bigquery 中有一个带有 TIMESTAMP 列“register_date”的数据集(示例值“2017-11-19 22:45:05.000 UTC”)。

我需要根据今天之前的 x 天或几周来过滤记录。
示例查询
选择所有 2 周前的记录。

目前我有这个查询(我觉得这是一种黑客)可以工作并返回正确的结果

SELECT * FROM `my-pj.my_dataset.sample_table`
WHERE
 (SELECT
  CAST(DATE(register_date) AS DATE)) BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -150 DAY)
  AND CURRENT_DATE()
LIMIT 10

我的问题是我是否必须在 TIMESTAMP 列上使用所有 CASTing 的东西(这似乎使原本简单的查询变得过于复杂)？
如果我删除 CASting 部分，我的查询不会运行并返回错误。

这是我的简化查询

SELECT
  *
FROM
  `my-pj.my_dataset.sample_table`
WHERE
  register_date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -150 DAY)
  AND CURRENT_DATE()
LIMIT
  10

导致错误

Query Failed
Error: No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:17]

任何见解都受到高度赞赏。

Answer 1

使用 timestamp职能:

SELECT t.*
FROM `my-pj.my_dataset.sample_table` t
WHERE register_date BETWEEN TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -150 DAY) AND CURRENT_TIMESTAMP()
LIMIT 10;

BigQuery 为日期/时间值提供三种数据类型: date , datetime , 和 timestamp .这些不能相互互换。基本思想是:

Dates没有时间组件和时区。

Datetimes有一个时间组件，没有时区。

Timestamp有时间组件和时区。实际上，它代表了UTC 中的值。

INTERVAL值在 gcp documentation 中定义
不同值之间的转换不是自动的。您的错误消息表明 register_date真正存储为 Timestamp .
一个警告(根据个人经验):天的定义基于 UTC。如果你在伦敦，这不是什么大问题。如果您在另一个时区并且希望“天”的定义基于本地时区，这可能是一个更大的问题。如果这对您来说是个问题，请提出另一个问题。