时间戳

首页 > 解决方案 > 在时间戳中选择范围并创建包含相应年份季节 bigquery 的新列

sql - 在时间戳中选择范围并创建包含相应年份季节 bigquery 的新列

问题描述

我想从 DateTime 列中选择 2 个时间戳之间的范围，创建新列 CropYear 并将 2019 应用于第一个选定范围，将 2020 应用于第二个。

期望的输出：

DateTime                  CropYear

'2018-11-16 00:00:00'       2019
...                         2019
'2019-10-14 23:59:59'       2019
'2019-10-15 00:00:00'       2020
...                         2020
*CURRENT_TIMESTAMP()*       2020

我试过这个查询，但似乎 CASE 不支持时间戳：

select SerialNumber, DateTime,
case DateTime
when DateTime BETWEEN '2018-11-16 00:00:01' AND '2019-10-14 23:59:59' then 2019
when DateTime BETWEEN '2019-10-15 00:00:00' AND CURRENT_TIMESTAMP() then 2020
else "error"
end
as CropYear
from `xx.yyy`

标签： sqlgoogle-bigquery

解决方案

只提取带有偏移量的年份函数怎么样？

with t as(
      select timestamp('2018-11-16 00:00:00') as dt union all
      select timestamp('2019-10-14 23:59:59') union all
      select timestamp('2019-10-14 00:00:00') union all
      select timestamp('2019-10-15 00:00:00') union all
      select timestamp('2019-10-16 00:00:00') union all
      select current_timestamp
     )
select t.*,
       extract(year from timestamp_add(dt, interval 78 day)
              ) as crop_year
from t;

这消除了对case表达式的需要。

“78”天是一个神奇的天数。将其添加到 10 月 15 日，您将得到 1 月 1 日——即次年。

一个注意事项 - 与问题没有直接关系 - 是要小心。 TIMESTAMP在 BigQuery 中是 UTC（基本上认为伦敦时间）。我猜您更关心特定时区。如果时区是一个问题，请提出一个新问题。

推荐阅读