Partition timestamp WHERE filters convert timezone incorrectly

[stevenschlansker]

Bug Report

Grafana-bigquery seems to use incorrect timezone conversions while expanding the $__timeFilter macro. Due to #309 the filter is repeated twice, but neither seems to indicate timezone correctly. Since the filters do not overlap, no rows are returned.

BigQuery data has timestamp column stored as UTC. Grafana is configured with UTC system timezone, and the dashboard uses browser local US Pacific Time.

This also might be related to #308, but since it's not entirely clear that it will fix this I figured I'd open an issue.

Expected Behavior

Without fixing #309, the expectation would be that the twice-repeated filter would be redundant but since both ranges are the same there is no issue.

Actual Behavior

Neither filter seems to correctly handle the timezone conversion

Right now is 15:22. When configuring the dashboard to "last 5 minutes", the following query shows up in the query inspector:

WHERE
  `timestamp` BETWEEN TIMESTAMP_MILLIS (1615274231454) AND TIMESTAMP_MILLIS (1615274531454) AND
  timestamp >= '2021-03-08 15:17:08' AND
  timestamp < '2021-03-08 15:22:08'

The first BETWEEN clause is checking between 23:17 and 23:22 PST (+8h from what it should be)
The latter clauses render a local time when they are comparing to a utc timestamp.

Since the ranges are disjoint, no results are returned, and the graph errors Object is not iterable

Steps to Reproduce the Problem

1. Use a UTC timestamp column as the table partition key
2. Create a dashboard in Grafana using that column as the "Time column"
3. Apply default $__timeFilter where clause

Specifications

• Version: 2.0.1
• Platform: Grafana Alpine docker container running on Google Kubernetes Engine
• Grafana Version: 7.5.0beta1

[ofir5300]

Hi @stevenschlansker ,

1. The first issue of the timestamp duplications is currently work in progress and will be fixed in the next release (regarding Use getTime for UTC conversion #308, Using the partition column as timeColumn results in that column appearing multiple times in the generated WHERE clause #309).
2. Regarding the issue of the 8 hours offset, possible explanation will be the local time on your docker container machine.
   Can you double check it?

[ofir5300]

@stevenschlansker please feel free to open another issue if the offset problem you mentioned still occurs.