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
What is your configuration of “Use the Java Virtual Machine (JVM) timezone” in Admin > Databases > (your bq)
And post “Diagnostic Info” from Admin > Troubleshooting.
@madkap
Have you tried enabling JVM timezone?
You can see the query being used when grouping - either by using the “Show the SQL” or looking at the request data in the browser developer console.
I’m guessing you’re seeing the warning triangle because the timezones doesn’t match name.
My reporting time is set to 'US/Pacific' so everything appears consistent except the output of timestamp_trunc in metabase UI (csv output is correct). It appears that there is some kind of timezone conversion happening with timestamp_trunc so it is showing the previous day in the UI. Is this expected behavior? Is there any way to have metabase ui show correct output for timestamp_trunc?
Thanks for the info. The export is not currently causing me any issues I’m curious why timestamp_trunc in the MB UI is not outputting expected results. This is affecting how the question builder does group by “day”.
For example in my above example if
completed_at = Nov 4, 2020, 10:03 AM
and I ran timestamp_trunc(completed_at,day), I would expect the output to be Nov 4, 2020.
However I’m getting output Nov 3, 2020, 4:00 PM.
It seems like MB is truncating & converting the timestamp instead of just truncating.
Am I misunderstanding how this is related to the github issue you linked?
@madkap I cannot reproduce, but you’re more than welcome to open an issue with steps-to-reproduce (since this involves a specific database, then a sample of that is needed).
The BigQuery driver has a long-standing issue where DATE values seem to get converted into the local timestamp equivalent (using the JVM timezone) of the UTC day at midnight. For date based graphs where you format the date without a time, this manifests as days rather silently being off by one. I’ve gotten in the habit of casting all dates to STRING to work around this—metabase processes those correctly as dates without converting it to a UTC datetime and your charts and tables will show the value properly.
Sidenote: I feel lots guilt writing about this asinine workaround that I’ve made a habit for a year+ without knowing whether or not an existing issue actually captures this. There have been quite a few timezone issues with BQ that have been reported (and many resolved), but I haven’t taken the time to look through the current ones to make sure it’s recorded there somewhere. I’ll look around again and open an issue if I can’t find one.
Thanks for the info, I tried your workaround and it seems that it works when viewing the data in a table but when I try to use the question builder and group by day I still get the “offset by 1 day” issue. I think what is happening is that even though the “correct” date time string is being passed while running the query, when the field comes back to metabase, it is converted back to UTC again.
Were you able to use the question builder and group by day and see correct results etc?
So far I’ve not found a workaround for date grouping in the query builder. Most of the data I expose to Metabase is through the use of views though (typically just augmenting an existing source table with calculated columns that I can just pull into Metabase for convenience), so I do have a few tables where I’ve baked in a day_string column that can be used for display without manually casting each time.
on the first post