I’m pulling in 2 of the exact same tables into metabase but one is from postgres and one is from bigquery. The bigquery table is replicated directly from the postgres table via (create table bq_table as select * from postgres_table). Metabase is set to US/Pacific report time zone.
My timestamp field is “completed_at”. When I try to do something simple with the BQ data source like count rows group by completed_at:day I get very strange results, the daily counts are off by significant amounts and the most current day is missing. The same operation on the postgres data source yields results as expected.
I did a spot check by querying a specific line item directly in postgres as well as directly in bigquery console
postgres: 2020-10-26 19:51:09
bigquery : 2020-10-27 02:51:09 UTC
I also looked up the same line item in metabase using the SQL editor
postgres: Oct 26, 2020, 7:51 PM
bigquery : Oct 26, 2020, 7:51 PM
It appears that bigquery converted my postgres PST timestamp to UTC and metabase converted the bigquery UTC time value back to my local time (PST) so everything appears to be fine.
However here is what a results of the simple count rows group by competed_at:day looks like
Postgres
Bigquery
I also saw this strange alert in the corner of the visualization view in the bigquery version of the question.
Any ideas what could be causing this and how to resolve?