I'm using the query editor to sum some time series data for the month of March 2024 and I noticed that the result wasn't matching the value I get when querying our database natively.
We use CET as our display time zone on Metabase, so I expect Metabase to filter the data with regards to CET when applying a date filter to the query (March 2024 in this case). When inspecting the SQL constructed by the query builder, I get this:
As you can see from the SQL, Metabase uses UTC+1 for both the start and end of the date range. But since daylight savings in CET happened on March 31st at 02.00 AM local time in 2024, the end of the date range should be compared to UTC+2.
The result of this bug is that (in our case) data for the first hour of April is included in the sum when it shouldn't be.
Has anyone else run into the same issue? If so, have you found a solution or workaround?
I'm using Metabase Cloud ( v1.49.8) and running Google BigQuery.
Hey christianschmidt
Check something for me when you have a minute.
Can you do a one for one comparison between the data in BigQuery and your questions in Metabase.
From the screenshot it looks like timestamps that have a 00:00:00 in BigQuery are getting the +1 in Metabase instead of +2.
What about timestamps in BigQuery that have anything but 00:00:00?
Hey Filmon, thanks for getting back to me.
The underlying time series data is identical in Metabase and BigQuery. The data is stored in UTC and Metabase converts it to the display time zone of CET when displaying the data. So all good on that front.
The issue is how the inequality check for the date range is constructed when the date range spans a period with daylight savings. In this case the full month of March 2024, where the end of the date range used in the inequality check is using +1 when +2 would be correct.
From my testing, this issues seems to only appear when the end date of the range is on the specific date where daylight savings takes place. If I pick April 1st as the end date, the query correctly uses +2 for the end date check.
Hey christianschmidt
I just realized I didn’t enter my complete message originally, sorry for the confusion on my part.
Judging from the screenshot it looks like it’s using the BETWEEN so I’m curious if you’re able to use the time component on a filter on your end to narrow it down as that should work.
