Hello,
When creating a simple query in the UI editor to count total rows over a date range, I used the filter to select "previous 3 months, starting 2 months ago". The results seem fine, however when using the SQL editor to build the same query but with a date variable the results are very different (approx 40% higher). The version with the date variable is the one that looks incorrect.
Applying the filter to the UI question, then converting to SQL gives the code:
AND DATE_ADD(
dataset.table.date, INTERVAL 2 month) BETWEEN DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -3 month), month)
AND DATE_TRUNC(CURRENT_DATE(), month)
I can experiment with using this in the native query editor or using an [[AND {{date}}]] variable with the same filter selection, but the results are different.
The "date" field is set to "Creation date" as the data type.
In this specific example, the date range is May 1st - July 31st:
and again when I run a specific query for this date range the results are different:
Diagnostic info:
{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/106.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.16.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16.1+1",
"os.name": "Linux",
"os.version": "5.4.104+",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"bigquery-cloud-sdk"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.12 (Ubuntu 12.12-0ubuntu0.20.04.1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.5.0"
}
},
"run-mode": "prod",
"version": {
"date": "2022-09-29",
"tag": "v0.44.4",
"branch": "release-x.44.x",
"hash": "382d728"
},
"settings": {
"report-timezone": "UTC"
}
}
}
I'm not seeing any errors in the logs.
Many thanks,
David