Why am I unable to use a timezone converted column to filter a day partitioned table?

Question

I have a BigQuery table with timestamp column. By nature we need to store time in BigQuery as UTC.

But for visualization in Looker Studio via materialized views I need the time to be in local timezone.

So I am trying to convert the timezone when creating the materialized view. but then the query doesn't recognized the filter on timestamp is for the partition column and the below error pops up:

Table requires a partition filter. Materialized views over a table that requires a partition filter must have a filter over the table's partitioning column or output the column.

To create the materialized view I am using the below query

CREATE MATERIALIZED VIEW `AirView.HourAvg`
  PARTITION BY date(timestamp)
  OPTIONS (
  refresh_interval_minutes=60
) AS (
SELECT
  b.thing_name,
  b.city,
  b.device_type,
  a.thing_id,
  IF (b.device_type="Stationary", b.latitude, a.latitude) as latitude,
  IF (b.device_type="Stationary", b.longitude, a.longitude) as longitude,
  DATETIME(DATETIME_TRUNC(a.time, MINUTE), "Asia/Kolkata") as timestamp
FROM (
  SELECT
    *, (no2 * 1.88) AS _no2, (so2 * 2.62) AS _so2
  FROM
    `AirView.FilteredData`
  )a
JOIN
  `AirView.ThingsTable` b
ON
  b.thing_id = a.thing_id
group by 1,2,3,4,5,6,7
)

I need a way to convert timezone of timestamp column when querying on a day partitioned table with the same timestamp column as a filter.

Answer 1

Try to provision an original timestamp column, then use it as a partition.

CREATE MATERIALIZED VIEW `test.HourAvg`
  PARTITION BY date(original_timestamp)
  OPTIONS (
  refresh_interval_minutes=60
) AS (
SELECT
  b.thing_name,
  b.city,
  b.device_type,
  a.thing_id,
  IF (b.device_type="Stationary", b.latitude, a.latitude) as latitude,
  IF (b.device_type="Stationary", b.longitude, a.longitude) as longitude,
  a.time as original_timestamp,
  DATETIME(DATETIME_TRUNC(a.time, MINUTE), "Asia/Kolkata") as timestamp
FROM (
  SELECT
    *, (no2 * 1.88) AS _no2, (so2 * 2.62) AS _so2
  FROM
    `test.FilteredData`
  )a
JOIN
  `test.ThingsTable` b
ON
  b.thing_id = a.thing_id
group by 1,2,3,4,5,6,7,8
)