title: Group and aggregate by day against UTC dates, adjusted to a specified timezone offset
body: <p>I have a query where I'm determining an overall <code>status</code> for a particular day, based on aggregating data by a UTC date in BigQuery, so that the resulting data will have this form:</p>
  
  <pre><code>date            status
  ----            ------
  28-feb-2019     0
  01-mar-2019     1
  </code></pre>
  
  <p>Here's the query, where <code>sample_date_time</code> is the UTC date in BigQuery. <code>@startDateTime</code> and <code>@endDateTime</code> are currently passed as UTC dates that always represent a UTC day boundary e.g.</p>
  
  <p><code>@startDateTime = '2019-02-28T00:00:00.000Z'</code></p>
  
  <p><code>@endDateTime = '2019-03-01T00:00:00.000Z'</code></p>
  
  <pre><code>select CAST(sample_date_time AS DATE) as date,
         (case when sum(case when status_code &gt;&gt; 0 = 0 then 1 else 0 end) &gt; 0 
               then 0 
          else 
               case when sum(case when status_code &gt;&gt; 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
  </code></pre>
  
  <p>I need to convert my query so that it can instead aggregate the data based on day boundaries <em>for a given timezone</em>. The query should return an ordered sequence with a column that represents the day number relative to the given timezone and the supplied date range. To clarify, I need the data to take the following form:</p>
  
  <pre><code>day            status
  ----           ------
  1              0
  2              1
  </code></pre>
  
  <p><code>@startDateTime</code> and <code>@endDateTime</code> will be passed as ISO_8601 dates that will always represent a day boundary in a given timezone, and will be in the format that supplies the timezone offset relative to UTC e.g. :</p>
  
  <p><code>@startDateTime = '2019-02-28T00:00:00+11:00'</code></p>
  
  <p><code>@endDateTime = '2019-03-01T00:00:00+11:00'</code></p>
  
  <p>So, the <code>status</code> for day 1 will be aggregated between <code>2019-02-28T00:00:00+11:00</code> and <code>2019-03-01T00:00:00+11:00</code></p>
  
  <p>Assuming that I can pass the <code>offset</code> into the query as a parameter, and that <em>efficiency isn't a significant consideration</em> (I'm looking for a quick solution in a self-contained query), how can I perform the grouping, and return the day number?</p>
  
  <p>BigQuery doesn't appear to have a <code>convert</code> function, so I don't appear to be able to use something like this in my <code>group by</code>:</p>
  
  <pre><code>group by convert(sample_date_time, dateadd(hours, offset, sample_date_time))
  </code></pre>
  
  <p>Any advice on what I should be looking at to achieve this is appreciated.</p>