时间戳

首页 > 解决方案 > 根据 UTC 日期按天分组和聚合，调整为指定的时区偏移量

sql - 根据 UTC 日期按天分组和聚合，调整为指定的时区偏移量

问题描述

我有一个查询，我status根据 BigQuery 中的 UTC 日期聚合数据来确定特定日期的总体数据，以便生成的数据具有以下形式：

date            status
----            ------
28-feb-2019     0
01-mar-2019     1

这是查询，sample_date_timeBigQuery 中的 UTC 日期在哪里。@startDateTime并且@endDateTime当前作为始终代表 UTC 日边界的 UTC 日期传递，例如

@startDateTime = '2019-02-28T00:00:00.000Z'

@endDateTime = '2019-03-01T00:00:00.000Z'

select CAST(sample_date_time AS DATE) as date,
       (case when sum(case when status_code >> 0 = 0 then 1 else 0 end) > 0 
             then 0 
        else 
             case when sum(case when status_code >> 0 = 1 then 1 else 0 end) = 1
             then 1
             end
        end) as status 
from (
  with data as
    (
      select
        sample_date_time,
        status_code
      from `my.table` 
      where sample_date_time between @startDateTime and @endDateTime
      order by sample_date_time
    )

  select sample_date_time, status_code
  from data
)
group by date
order by date

我需要转换我的查询，以便它可以根据给定时区的日期边界聚合数据。查询应返回一个有序序列，其中有一列表示相对于给定时区和提供的日期范围的天数。为了澄清，我需要数据采用以下形式：

day            status
----           ------
1              0
2              1

@startDateTime并将@endDateTime作为 ISO_8601 日期传递，该日期将始终表示给定时区中的日期边界，并将采用提供相对于 UTC 的时区偏移的格式，例如：

@startDateTime = '2019-02-28T00:00:00+11:00'

@endDateTime = '2019-03-01T00:00:00+11:00'

因此，第status1 天的数据将在2019-02-28T00:00:00+11:00和2019-03-01T00:00:00+11:00

假设我可以将'offset作为参数传递到查询中，并且效率不是一个重要的考虑因素（我正在寻找独立查询中的快速解决方案），我如何执行分组并返回日期数字？

BigQuery 似乎没有convert功能，所以我似乎无法在我的group by:

group by convert(sample_date_time, dateadd(hours, offset, sample_date_time))

任何关于我应该看什么来实现这一点的建议都值得赞赏。

标签： sqlgoogle-bigqueryaggregatetimezone-offset

解决方案

我会使用时区转换数据库中的日期。就个人而言，我经常这样做：

select date(sample_date_time, 'America/New_York') as dte, count(*)
from t
group by dte;

这只是作为示例。您的查询显然更复杂。

推荐阅读